homepage Welcome to WebmasterWorld Guest from 54.161.236.229
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved