Welcome to WebmasterWorld Guest from 23.22.46.195

Forum Moderators: open

Output table with a count from another table

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

5+ Year Member Top Contributors Of The Month



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.
7:18 pm on Aug 30, 2011 (gmt 0)

5+ Year Member Top Contributors Of The Month



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"
9:52 pm on Aug 30, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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 ;-)
10:44 pm on Aug 30, 2011 (gmt 0)

5+ Year Member Top Contributors Of The Month



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?
11:47 pm on Aug 30, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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.
10:39 am on Aug 31, 2011 (gmt 0)

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month