CODEWITHSUNDEEP
mysql
DolDurma
DolDurma

Reputation: 17321

MySql get different output from single sql command

In MySql database I have three tables as : channels, channels_members and users, channels_members linked to channels with channel_id and channels linked to users with user_id.

In channels table I'm storing created channels by users, each channel can be is_auto and is_admin or is_user, all of channels which they are flagged with 1 on is_auto that means: all users joined to that automatically, otherwise channel member is admin or user, now what do I want to do?

1: I want to get all channels which is_auto column equals with 1 without considering member role on channels_members table.

2: Get all channels with member role which that specific on channels_members table when is_admin is 1 and then

3: Get all channels with member role which that specific on channels_members table when is_user is 1 on this channel, for example: I have 4 channels

channels table:

id     channel_name   is_auto    user_id
1      debian           1          1
2      ubuntu           0          1
3      mint             0          1
4      fedora           0          1

channels_members table:

id     user_id        channel_id    is_admin   is_user 
1      1              1                0       1
2      1              2                1       0
3      1              3                0       1
4      1              4                0       1

output should be like this result

id     channel_name   is_auto    user_id   is_admin   is_user 
1      debian           1          1          0       1
2      ubuntu           0          1          1       0
3      mint             0          1          0       1
4      fedora           0          1          0       1

My command don't return correct result for this output

SELECT channels.channel_name FROM `channels` 
JOIN channels_members on channels.id = channels_members.channel_id 
JOIN users on users.id = channels.user_id where channels.is_auto =1 
OR channels_members.is_admin =1 
OR channels_members.is_user =1 and users.id = 1 
GROUP By channels.id

Upvotes: 0

Views: 42

Answers (2)

lucky b
lucky b

Reputation: 1

Try the next query, hoping it helps... 

SELECT channels.channel_name,channels.is_auto, channels.user_id, channels_members.is_admin, channels_members.is_user FROM `channels` 
    INNER JOIN channels_members on channels.user_id = channels_members.user_id 
    GROUP By channels.id

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can join channels_members twice with channels by adjusting the on clause part

select c.*,
coalesce(m1.is_admin,0) is_admin,
coalesce(m2.is_user,0) is_user
from `channels` c
left join channels_members m1 on c.id = m1.channel_id and m1.is_admin =1
left join channels_members m2 on c.id = m2.channel_id and m2.is_user =1
/* join with users table */
where c.user_id = 1 
order by c.id

DEMO

Upvotes: 1

Related Questions