Output table with a count from another table

Forum Moderators: open

Message Too Old, No Replies

Output table with a count from another table

ocon

3:52 pm on Aug 30, 2011 (gmt 0)

I have two tables: 'places', 'reviews'

'places':
- id, int(10), unsigned, auto_increment, PRIMARY
- name, varchar(255)
- address, text

'reviews':
- id, int(10), INDEX
- comment, text

Each row has a unique id in the 'places' table, where as in the 'reviews' table, there can be multiple rows with the same id.

What I'm trying to do is to query my 'places' table but to create a new column called reviews that has the count of how many rows in the 'reviews' table matches the id in a given row in the 'places' table.

My current query looks like this:

"SELECT * FROM places"

Any help is greatly appreciated.

ocon

7:18 pm on Aug 30, 2011 (gmt 0)

Let me add that when I try something like the following query it only outputs one row and doesn't give me a count, though it does seem closer to the right answer than my basic query above:

"SELECT *, COUNT(reviews.id) AS review_count FROM places, reviews WHERE places.id = reviews.id"

Dijkgraaf

9:52 pm on Aug 30, 2011 (gmt 0)

Something like

select places.id, places.name, places,address, (select count(1) from reviews where reviews.id = places.id) as review_count
from places

You could have gone further with your select

SELECT *, COUNT(reviews.id) AS review_count FROM places, reviews WHERE places.id = reviews.id

by changing it to the below.

"SELECT places.id, places.name, places.address, COUNT(reviews.id) AS review_count FROM places, reviews WHERE places.id = reviews.id"
GROUP BY places.id, places.name, places.address

Hopefully I have the syntax correct as I haven't run them ;-)

ocon

10:44 pm on Aug 30, 2011 (gmt 0)

Thank you, that was incredibly helpful and worked great. I used your first recommendation.

My only question is I don't understand the count(1) part. Is that because the id is the first column in the reviews table?

Dijkgraaf

11:47 pm on Aug 30, 2011 (gmt 0)

You could put either count(1) or count(*) it doesn't make much of a difference (none in the results), but there is some debate about performance of one versus the other. Do a search on count(1) and you will find various pages either saying one or the other is better or that they are both the same.

The count(1) just says count the number rows based on a pseudo column with he value 1.

penders

10:39 am on Aug 31, 2011 (gmt 0)

Just a curiosity... do you not have a primary key on your reviews table?