Nested Join question

[n8w]

I have 3 tables

 

favorite

-favorite_id

-user_id

-letter_id

 

user

-user_id

-user_status

 

letter

-letter_id

-user_id

-letter_visible

 

I would like to count how many favorites a user has

SELECT letter_id,user_id, COUNT(user_id) as the_count FROM favorite GROUP BY user_id

 

but now I want to count how many favorites a favorite.user_id has where the user.user_status=active and the letter.letter_visible=1

 

The tricky part is favorite.user_id is the person who marked the letter as their favorite not the person who created it

So I have to find who created it by looking up favorite.letter_id and seeing if their user.user_status=active

 

So in plain english I would like to find

How many favorites a user has where the status of the person who created the letter is active and the status of the letter is visible.

 

Thanks for any help!

 

 

 

[BioBob]

SELECT letter_id,

          user_id,

          COUNT(user_id) AS the_count

FROM favorite

LEFT JOIN `user` ON `user`.`user_id` = `favorite`.`user_id`

LEFT JOIN `letter` ON `letter`.`user_id` = `letter`.`user_id`

WHERE user.user_status=active AND letter.letter_visible=1

GROUP BY user_id

HAVING COUNT(user_id) > 0

 

I think that will help.  You might have to specify each field with the db its being selected from or you might get a NOT AMBIGUOUS error.

[n8w]

Hey Biobob,

 

Thanks for replying

 

I think I am calling the wrong tables because I am getting a crazy count

 

sql

SELECT favorite.letter_id, favorite.user_id, COUNT( favorite.user_id ) AS the_count
FROM favorite
LEFT JOIN  `user` ON  `user`.`user_id` =  `favorite`.`user_id` 
LEFT JOIN  `letter` ON  `letter`.`user_id` =  `letter`.`user_id` 
WHERE user.user_status =  'active'
AND letter.letter_visible =1
GROUP BY user.user_id
HAVING COUNT( letter.user_id ) >0
LIMIT 10

 

results should be in the 100 range

letter_id	user_id	the_count
89	1	40230
31	3	894
140	17	1341
85	23	2235
104	26	1788
147	27	9387
46	30	447
128	31	894
104	34	24138
267	39	447

 

Do you see where I am going wrong? Thanks!

[fenway]

You can't have user_id in there with group by like that.