Jump to content

Nested Join question


n8w
 Share

Recommended Posts

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!

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.