| Output table with a count from another table
|
ocon
msg:4356738 | 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
msg:4356798 | 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
msg:4356872 | 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
msg:4356897 | 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
msg:4356910 | 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
msg:4357053 | 10:39 am on Aug 31, 2011 (gmt 0) |
Just a curiosity... do you not have a primary key on your reviews table?
|
|
|
