CODEWITHSUNDEEP
mysqlsql
StudioTime
StudioTime

Reputation: 23959

Join query returning empty as well

I have three tables (many more cols but listed important ones):

users
user_id - name
1         DAN
2         TED
3         SAM

list_shares
list_shares_id - user_id - list_id
1                1         123
2                3         123
3                2         456

list_contribute
list_contr_id - list_id - can_contribute
1               123       3

I want to show all users who are in table list_shares under a list_id, join that with users table to get user info, and also count how many of them are also in the contribute table

Basically - a user can share a list with users and also invite some of the users to contribute, but not all those sharing are allowed to contribute, hence the separate list_contribute table.

here's what I'm using firstly which just shows all users with a certain ID:

select u.name, u.live_prof_pic, u.url, u.user_id from list_shares ls 
join users u on ls.user_id = u.user_id
where ls.list_id = '123'

This brings up two results, which is correct - the next query is trying to find which of these two are in the list_contribute table also - but, it reduces the result to one whereas i want it to return both and show 0 for how_many if not in list_contribute

select u.name, u.live_prof_pic, u.url, count(ls.list_shares_id) as how_many, u.user_id from list_shares ls
join users u on ls.user_id = u.user_id
left join list_contribute lc on ls.list_id = lc.list_id
where ls.list_id = '123'

In the above data i want to return

user_id    name    how_many    
3          SAM     1
1          DAN     0

Upvotes: 0

Views: 79

Answers (3)

roman
roman

Reputation: 117337

If I got you right - here's what you need

SQL Fiddle Example

select
    u.user_id, u.name,
    count(case when lc.can_contribute = u.user_id then 1 else null end) as HowMany
from users as u
    inner join list_shares as ls on ls.user_id = u.user_id
    inner join list_contribute as lc on lc.list_id = ls.list_id
group by
    u.user_id, u.name

Upvotes: 1

Farfarak
Farfarak

Reputation: 1517

Use query below if it does not return desired result set, it means you have orphaned record sets in list_shares which does not have corresponding users.

    select 
          u.name
        , u.user_id   
        , count(lc.list_id) as how_many
    from 
        list_shares ls
    INNER JOIN
        users u 
    on 
        ls.user_id = u.user_id
    left join 
        list_contribute lc 
    on 
        ls.list_id = lc.list_id
    where 
        ls.list_id = '123'
    GROUP BY
          u.name
        , u.user_id

Not sure how mysql count works so to be 100% sure:

    select 
          u.name
        , u.user_id   
        , SUM(CASE WHEN lc.list_id IS NOT NULL THEN 1 ELSE 0 END CASE) as how_many
    from 
        list_shares ls
    INNER JOIN
        users u 
    on 
        ls.user_id = u.user_id
    left join 
        list_contribute lc 
    on 
        ls.list_id = lc.list_id
    where 
        ls.list_id = '123'
    GROUP BY
          u.name
        , u.user_id

Upvotes: 1

gbn
gbn

Reputation: 432180

Add a GROUP BY to make it a standard SQL aggregate

select u.name, u.live_prof_pic, u.url, count(ls.list_shares_id) as how_many, u.user_id
from 
list_shares ls
join users u on ls.user_id = u.user_id
left join list_contribute lc on ls.event_id = lc.event_id
where ls.list_id = '123'
group by u.name, u.live_prof_pic, u.url, u.user_id

MySQL has a rubbish extension that tries to remove the requirement but gives incorrect data often

Edit, after comment.

You should still use correct GROUP BY syntax

However, your COUNT should be COUNT(lc.event_id) to count child rows.

Upvotes: 1

Related Questions