Monday, April 14, 2008

SQL Join with count

For the real estate site I've been working on, I needed to get a list of real estate offices and how many active listings they had on the MySQL DB we are using. I had a table of offices and a table of listings. I needed each office represented even if that office had no active listings. It seemed like an easy application of the left join command. Not quite as easy as I thought. Here's my first attempt:


SELECT
COUNT(listings.id), offices.id
FROM offices LEFT JOIN listings ON (offices.id = listings.office_id)
WHERE
listings.status = 1


However, this only returned office/count pair where the count was greater than 0. It seemed like the WHERE condition wasn't being applied correctly, like it was being applied AFTER the tables were joined. I wanted the WHERE condition to be applied to the listings table and then have the results joined to the offices table. So, here's the solution I found: in situations like this, the WHERE clause needed to be added to the ON clause in the LEFT JOIN like this:


SELECT
COUNT(listings.id), offices.id
FROM offices LEFT JOIN listings ON (offices.id = listings.office_id AND listings.status = 1)


Works like a charm.

4 comments:

Danny said...

PERFECT!!

I was just having the same problem!

Now i can count and still show the categories, even if they are empty!

Thanks A MILLION!

Danny

Persuadis said...

Thanks a lot! Had the same problem!

alexey said...

the trick is done simply by adding "ORDER BY ..." (:

mineass said...

Damnit, y didnt i figured it out by myself lol ty