user2782582 user2782582 - 1 month ago 22
SQL Question

SQL check if value exists in group

Say I have a table of channel members

channelID | accountID | role
------------------------
1 | Bob | 100
2 | Bob | 100
1 | Dave | 101


and another table of channels

channelID | channelName | type
---------------------------------------
1 | Public Channel | public
2 | Private Channel | private


How would I go about getting channels that a user is not in that are public?

I attempted doing this but cannot figure out how to group channel members table since there are two items with channelID 1 so when I do my query if I search with user Dave I get channel 1 as a result when it should return nothing. My query that I'm using:

Select ChatChannels.channelID, channelName, type
From ChatChannels
INNER JOIN ChatChannelMembers
On ChatChannels.channelID = ChatChannelMembers.channelID
Where AccountID = 'Dave'

Answer

Does this work?

SELECT (stuff)
FROM ChatChannels CC
WHERE CC.type = 'Public'
  AND CC.channelID NOT IN (SELECT channelID FROM ChatChannelMembers WHERE AccountID = 'Dave')
Comments