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

"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."

If the query is returning the correct records then you've got the hard part done. Just alter which fields you get back
fromit then.
 

Instead of: "select * from..."
Go with either: "select u.* from..."
Or: "select u.id, u.name from..."


-----Original Message-----
From: Pól Ua Laoínecháin [mailto:linehanp@tcd.ie] 
Sent: Tuesday, April 23, 2019 12:14 PM
Cc: pgsql-novice
Subject: SELECTing for group membership of many groups?

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);