Get members of a given group from N:M relationship

Question

I have this N:M relationship:

CREATE TABLE auth_user (
    id integer NOT NULL PRIMARY KEY,
    username character varying(150) NOT NULL UNIQUE
);

CREATE TABLE auth_group (
    id integer NOT NULL PRIMARY KEY,
    name character varying(80) NOT NULL UNIQUE
);

CREATE TABLE auth_user_groups (
    id integer NOT NULL PRIMARY KEY,
    user_id integer REFERENCES auth_user(id) NOT NULL,
    group_id integer REFERENCES auth_group(id) NOT NULL,
    CONSTRAINT user_groups UNIQUE(user_id, group_id)
);

INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);

How to select all usernames which are in the group 'group1'?

I use PostgreSQL, but SQL which works everywhere is preferred.

Answer 1

This EXISTS query returns unique users, regardless of whether there can be duplicate entries in auth_user_groups.

SELECT *
FROM   auth_user u
WHERE  EXISTS (
   SELECT 1
   FROM   auth_user_groups
   WHERE  user_id = u.id
   AND    group_id = (SELECT id FROM auth_group WHERE name = 'group1')
   );

Typically fast, too.

Notes

For Postgres - which you seem to be using.

The sequence of index columns matters. You defined UNIQUE(user_id, group_id), which is implemented with a corresponding unique index. For your particular query, an index on (group_id, user_id) would be preferable.

You can either switch the columns of the UNIQUE constraint, or create an additional (optionally unique) index with the columns reversed if you need both (which is the common case). Related:

• Is a composite index also good for queries on the first field?

Also be aware that columns included in a UNIQUE constraint can still be NULL. (Unlike a PRIMARY KEY constraint which makes all member columns NOT NULL automatically!) You'd typically want user_id and group_id in auth_user_groups to be NOT NULL as well. Related:

• How to implement a many-to-many relationship in PostgreSQL?

Answer 2

Something like this:

select *
from auth_user
where id in (select aug.user_id
             from auth_group ag
               join auth_user_groups aug on aug.group_id = ag.id
             where ag.name = 'group1');

Answer 3

A_horse_with_no_name posted a good answer, in fact this was my primary option (deserved an upvote =))

You can also just do it the usual way:

select au.*
from dbo.auth_user au
join dbo.auth_user_groups aug on aug.[user_id] = au.id
join dbo.auth_group ag on ag.id = aug.group_id
where ag.[name] = 'group1'

But note, if you have more than one group with the same name and users in homonymous groups, you will get duplicate rows:

INSERT INTO auth_group VALUES (3, 'group1');
INSERT INTO auth_user_groups VALUES (1, 1, 3);

In this case you should use a distinct:

select distinct au.*
from dbo.auth_user au
join dbo.auth_user_groups aug on aug.[user_id] = au.id
join dbo.auth_group ag on ag.id = aug.group_id
where ag.[name] = 'group1'

Note A_Horse answer still works in this case.

Answer 4

From SQL Server 2017 and Azure SQL DB you can use the new graph database capabilities and the new MATCH clause to answer queries like this, eg

SELECT FORMATMESSAGE ( 'User %s (%i) is in group %s.', [user].userName, [user].userId, [group].groupName ) msg
FROM dbo.users [user], dbo.hasGroup hasGroup, dbo.groups [group]
WHERE [group].groupName = 'group1'
  AND MATCH ( [user]-(hasGroup)->[group] );

My results:

Full script available here.

Answer 5

select username from auth_user, auth_user_groups, auth_group where 
  auth_group.name='group1' and 
  auth_user_groups.group_id=auth_group.id and
  auth_user.id=auth_user_groups.user_id;