CODEWITHSUNDEEP
javasqldatabasesqlite
Tiim
Tiim

Reputation: 181

SQL get rows which are not in

I got three tables in my sqlite database called member, group and group_member.

The member table looks like this:

member_id|first_name|...

The group table like this:

group_id|name|...

And the group_member table like this:

group_id|member_id

Now I want the user of my application to add/remove members from a group by having two lists of people besides each other. The first list should contain all people that are not in the group and the second list contains all members of the group.

not in group          in group
------------------------------
person1 |            | member5
person2 |    Add ->  | member6
person3 |  <- Remove |
person4 |            |

I know how I can get all members in the group, but how do I get the members that do not belong to the group to fill the first list?

Upvotes: 2

Views: 43

Answers (2)

Andomar
Andomar

Reputation: 238116

You can use a left join to retrieve a flag for whether the member is in the group:

select  m.member_id
,       first_name
,       case when gm.group_id is not null then 1 else 0 end as CurrentlyInGroup
from    members
left join
        group_member gm
on      m.member_id = gm.member_id
        and gm.group_id = 42 -- The group you're editing

When the flag is 0, you put the member in the currently-not-a-member list.

Upvotes: 0

Sergii Lagutin
Sergii Lagutin

Reputation: 10671

select m.* from member m 
where not exists (
    select 1 
    from group_member gm
    where gm.member_id  = m.member_id and group_id = ? )

Upvotes: 1

Related Questions