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:

FROM offices LEFT JOIN listings ON ( = listings.office_id)
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:

FROM offices LEFT JOIN listings ON ( = listings.office_id AND listings.status = 1)

Works like a charm.


Danny said...


I was just having the same problem!

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



Persuadis said...

Thanks a lot! Had the same problem!

alexey said...

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

Anonymous said...

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