I'm trying to do the following.
I have three table:
Table a has address information:
address_id | City | State | Zip
1 Austin TX 78758
2 Dallas TX 77000
3 Galveston TX 77550
Table b has information about the location:
address_id | Location_id | Location_name
1 1 The Place
1 2 The Place Before
2 3 A shop
Table c has montlhy sales history
Locationid | MonthYear | Sales
1 2005-01-01 299
1 2005-02-01 100
1 2005-10-01 300
2 2005-01-01 154
3 2005-10-10 99
Not every location has sales information.
I am trying to create a query where I can SELECT the Locationname, City,
State, Zip, and the SUM(sales) if the place has sales. So far, despite
playing around with joins for more hours than one should ever dedicated to
the matter, I haven't been able to include SUM(sales) without excluding
listings without sales.
So far this is my best shot:
SELECT locationname, city, state, zip, SUM(sales) as 'Sales'
FROM a, b LEFT JOIN c ON (b.locationid = c.locationid)
WHERE a.address_id = b.address_id AND monthyear > "2005-01-01"
GROUP BY c.locationid
This however, will return only records with Sales and not those without it.
I haven't been able to force adding empty rows from table c... Doing "AND
c.locationid IS NULL" returns no results at all.
Any help would GREATLY be appreciated!!!