SELECTing for group membership of many groups? - Mailing list pgsql-novice

Hi all,

maybe this should be easy, but I'm bashing my head up against a brick wall!

DDL and DML for sample data at bottom of post.

Simplified - I have 3 tables, the_user (user is a keyword in some
systems), groupe (use the French to avoid conflicts with SQL keyword
GROUP!) and user_group.

Now, what I want is a simple list of all users who are in groups 5, 6
and 7 - in this case, there is only one - that's user 3.

I naively tried this:

SELECT * FROM the_user u
JOIN user_group ug ON
  u.id = ug.user_id
AND ug.group_id = 5
AND ug.group_id = 6
AND ug.group_id = 7;

But that won't work, because no single user_group record can have  a
group_id of 5, 6 **and** 7!

Then, there's this:

SELECT * FROM the_user u
INNER JOIN user_group g1 ON g1.user_id = u.id
INNER JOIN user_group g2 ON g2.user_id = u.id
INNER JOIN user_group g3 ON g3.user_id = id
WHERE g1.group_id = 5
  AND g2.group_id = 6
  AND g3.group_id = 7;


id name user_id group_id user_id group_id user_id group_id
3 user3          3            5          3           6          3           7

Now, this picks  out the_user.id = 3 which is correct, but I just want
the answer to be (3, 'user3') and not the group_ids which I've put
into the query.

There's an sql fiddle available here for those who are interested:
https://www.db-fiddle.com/f/gHaajr7txvTojV7WAjJbYb/2

TIA and rgs,

Pól...

============= DDL and DML ====================

CREATE TABLE the_user
(
  id INT,
  name VARCHAR (10)
);

CREATE TABLE groupe
(
  id INT,
  name VARCHAR (10)
);

CREATE TABLE user_group
(
  user_id INT,
  group_id INT
);

INSERT INTO the_user VALUES (1, 'user1'), (2, 'user2'), (3, 'user3');

INSERT INTO groupe VALUES (5, 'group1'), (6, 'group2'), (7, 'group3');

INSERT INTO user_group VALUES (1, 6), (1, 7), (2, 5), (2, 7), (3, 5),
(3, 6), (3, 7);