Welcome to WebmasterWorld Guest from 54.196.232.162

Forum Moderators: open

Message Too Old, No Replies

Output table with a count from another table

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

Full Member

5+ Year Member Top Contributors Of The Month

joined:Sept 30, 2009
posts:222
votes: 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.
7:18 pm on Aug 30, 2011 (gmt 0)

Full Member

5+ Year Member Top Contributors Of The Month

joined:Sept 30, 2009
posts:222
votes: 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"
9:52 pm on Aug 30, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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 ;-)
10:44 pm on Aug 30, 2011 (gmt 0)

Full Member

5+ Year Member Top Contributors Of The Month

joined:Sept 30, 2009
posts:222
votes: 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?
11:47 pm on Aug 30, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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.
10:39 am on Aug 31, 2011 (gmt 0)

Senior Member

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

joined:July 3, 2006
posts: 3123
votes: 0


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

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members