Forum Moderators: phranque
I have 4 tables: countries (id, name), states(id, name, conuntry id), cities (id, name, state id), sales (id, city id).
I have several ids of certain cities. I need to get country, state, city names and amount of sales in each of those cities. This is the hard part: I need to get either nothing or 0 if there are no sales in a city.
Current problem is that MySQL does not return anything if there are no sales in a city. Here is the syntax that I've tried.
SELECT cities.id as id, countries.name as country, provinces.name as province, cities.name as city, COUNT(sales.id) as amount
FROM countries, provinces, cities, sales
WHERE (sales.city = cities.id OR sales.city IS NULL)
AND cities.id IN ( 1114,1121,164 )
AND provinces.id = cities.province
AND countries.id = provinces.country
GROUP BY cities.id
ORDER BY cities.name
Can you please point out exactly what is wrong, or even better - tell me where I can read up on that?
I am sorry but if that does not help, and the user comments at the bottom of that page do not help. I dont think I can do a better job, but I will try.
The concept is very simple: if the inner join data is not there, do you still want to return the row along with a null? (then use left/right) Or do not return the row at all because the inner join does not exist?(then use inner)
Remember:
SELECT * FROM TableA INNER JOIN TableB ON TableA.id = TableB.CatId;
is the same as
select * from tablea,tableb where tablea.id=table.catid
Now just replace left join instead of inner join in the query and watch the results.. You will get it...
That is what I came up with and it works!
SELECT countries.name as country, provinces.name as province, cities.name as city, COUNT(sales.id) as amount
FROM cities LEFT JOIN sales ON cities.id = sales.city, provinces, countries
WHERE cities.id IN (1114,1121,164)
AND cities.province = provinces.id
AND provinces.country = countries.id
GROUP BY cities.id
ORDER BY cities.name, cities.id
