[SOLVED] Simple join statement question

[PKENGLISH]

Hi,

 

I'm trying to create what I think should be a really easy join statement however I'm pretty new to MySQL and am having a hard time wrapping my head around them.

 

What I have is as follows (MySQL version 5.0.16)

 

Table 1 "comments"

|  id  |  comment  |  time_stamp  |  name  |  approval_state  |

 

approval state can either be 0 or 1 where 1 is approved.

 

Table 2 "ratings"

|  id  |  comment_id  |  rating  |

 

ratings can be one of 3 ratings (1, 2, or 3).

 

The way I want to use these tables is print out all the comments that are approved (approval_state=1) and for each of those show a count of how many ratings they have that are 1, 2 and 3.  The 2 tables are related by comments.id=ratings.comment_id.

 

Thanks for the help in advance, please let me know if there is anything I can do to clarify.

 

[kickstart]

Hi

 

To have a single row returned per comment with the count of each of the 3 ratings:-

 

SELECT a.id, a.comment, a.time_stamp, a.name, a.approval_state, rating_1_Count, rating_2_Count, rating_3_Count
FROM comments a
INNER JOIN (SELECT comment_id, COUNT(id) AS rating_1_Count FROM ratings WHERE rating = 1 GROUP BY comment_id) b
ON a.id = b.comment_id
INNER JOIN (SELECT comment_id, COUNT(id) AS rating_2_Count FROM ratings WHERE rating = 2 GROUP BY comment_id) c
ON a.id = c.comment_id
INNER JOIN (SELECT comment_id, COUNT(id) AS rating_3_Count FROM ratings WHERE rating = 3 GROUP BY comment_id) d
ON a.id = d.comment_id
WHERE a.approval_state = 1
ORDER BY a.id

 

To have several rows per comment (one for a count of each used rating) then try the following.

 

SELECT a.id, a.comment, a.time_stamp, a.name, a.approval_state, b.rating, b.rating_Count
FROM comments a
INNER JOIN (SELECT comment_id, rating, COUNT(id) AS rating_Count FROM ratings GROUP BY comment_id, rating) b
ON a.id = b.comment_id
WHERE a.approval_state = 1
ORDER BY a.id, b.rating

 

All the best

 

Keith

[PKENGLISH]

Thanks Keith,

 

I think what I'm looking for is your first version but I'm still having some trouble getting results.  I transferred that query into Sequel Pro and ran it, no errors were returned but it also didn't return any results.  I have about 20 lines of trial comments available in my database as well and a bunch of ratings...  Any idea what might cause this to come back with an empty result?

 

Thanks again.

[kickstart]

Hi

 

I have used inner joins which will give you a returned record when there is one or more of each rating.

 

You can change them to LEFT OUTER JOINs if you want.

 

All the best

 

Keith

[PKENGLISH]

Perfect!  Thanks again!