[SOLVED] Multiple Left Joins Group, Count and a Where clause = twizzler brain

[amites]

Hello,

 

This one might make ya think, been racking my blain,

 

example of what I want to do:

 

SELECT cit.english name, Count(s.id) cnt

FROM

bil_cities AS cit

Join bil_location AS loc ON loc.city_id = cit.id

Join bil_msg_sent AS s ON s.locid = loc.id

WHERE cit.state_id =  '13'

GROUP BY cit.id

ORDER BY name ASC

 

DB Structure:

 

cities (every city in the US), location (contains, city_id, and loc_id)

bil_msg_sent (what I'm trying to count based on id & loc_id)

 

 

I'm trying to complete that query with Left Joins for bil_location and bil_msg_sent

to have a complete list of Cities within a given state,

 

of course once I change them to a Left Join it ignores the Where statement and tries to pull every city in the table,

 

any ideas?

[fenway]

Because you then need to move you where clause to the on clause.

[amites]

heh,

 

originally tried doing that with

 

FROM bil_city as cit ON cit.state_id = '13'

 

=

 

SQL error

 

 

---

 

 

then I realized that the state_id is also in the bil_location table

 

added an AND to the on clause for that line and boo yeah

 

---

 

thank you for the help