MySql get different output from single sql command

Question

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

M Khalid Junaid · Accepted Answer · 2017-09-23 11:07:10Z

1

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

answered Sep 23, 2017 at 11:07

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kenzo_Gilead · Accepted Answer · 2017-09-23 12:31:56Z

0

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

edited Sep 23, 2017 at 12:31

Kenzo_Gilead

2,4799 gold badges41 silver badges68 bronze badges

answered Sep 23, 2017 at 10:11

lucky b

11 bronze badge

1 Comment

DolDurma Over a year ago

i get this result:

channel_name 	is_auto 	user_id 	is_admin 	is_user 	      debian 	      0 	      1 	      0 	      1      ubuntu 	      0 	      1 	      0 	      1

Collectives™ on Stack Overflow

MySql get different output from single sql command

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related