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

i have sql server database 3 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 left join , got 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 join:

select g.groupid ,g.grouptitle ,mg.memberid ,ispartofgroup = case when mg.membersgroupid null 'no' else 'yes' end groups  g left join membergroups mg on g.groupid = mg.groupid left join members m on  m.memberid = mg.memberid 

but want 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, want members , groups part of , ones not part of.

is possible?

you need first cross join , left join on membergroups table. try this:

select g.groupid,        g.grouptitle,        m.memberid,        m.membername,        case when mg.membersgroupid null 'no' else 'yes' end ispartofgroup groups g cross join members m left join member_groups mg on g.groupid = mg.groupid , m.memberid = mg.memberid