CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Monik
Monik

Reputation: 47

Return records even if they don't exist in the joined tables

I have a SQL Server database with three tables:

GROUPS

GroupID GroupTitle
  1     Group 1
  2     Group 2
  3     Group 3
  4     Group 4

MEMBERS

MemberID MemberName
19       Jon
20       Marie
21       Andrew

MEMBER GROUPS

MembersGroupID GroupID  MemberID
 221              1      20
 231              1      21

I made a left join and got the following result: 

GroupID GroupTitle  MemberID IsPartOfGroup MemberName
1       Group 1     20       Yes           Marie
1       Group 1     21       Yes           Andrew
2       Group 2     NULL     No            NULL
3       Group 3     NULL     No            NULL
4       Group 4     NULL     No            NULL

This is my join:

select g.GroupID
,g.GroupTitle
,mg.MemberID
,IsPartOfGroup = case when mg.MembersGroupID is null then 'No' else 'Yes' end
from groups  g
left join membergroups mg on g.GroupID = mg.GroupID
left join members m on  m.memberid = mg.MemberID

But what I want is the following result:

GroupID GroupTitle  MemberID IsPartOfGroup MemberName
1       Group 1     19       No            Jon
2       Group 2     19       No            Jon
3       Group 3     19       No            Jon
4       Group 4     19       No            Jon
1       Group 1     20       Yes           Marie
2       Group 2     20       No            Marie
3       Group 3     20       No            Marie
4       Group 4     20       No            Marie
1       Group 1     21       Yes           Andrew
2       Group 2     21       No            Andrew
3       Group 3     21       No            Andrew
4       Group 4     21       No            Andrew

In other words, I want to get all members and the groups they are part of and also the ones they are not part of.

Is that possible?

Upvotes: 2

Views: 34

Answers (2)

Dika Arta Karunia
Dika Arta Karunia

Reputation: 484

select g.GroupID
,g.GroupTitle
,mg.MemberID
,IsPartOfGroup = case when mg.MembersGroupID is null then 'No' else 'Yes' end
from groups cross join membergroups 
left join members m on  m.memberid = mg.MemberID

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You need first to cross join and the left join on MemberGroups table. Try this:

select g.GroupID,
       g.GroupTitle,
       m.MemberID,
       m.MemberName,
       case when mg.MembersGroupID is null then 'No' else 'Yes' end as IsPartOfGroup
from GROUPS g
cross join MEMBERS m
left join MEMBER_GROUPS mg on g.GroupID = mg.GroupID and m.MemberID = mg.MemberID

Upvotes: 1

Related Questions