user2782582 user2782582 - 3 months ago 34
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'


Does this work?

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