From: | Pól Ua Laoínecháin <linehanp(at)tcd(dot)ie> |
---|---|
To: | |
Cc: | pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | SELECTing for group membership of many groups? |
Date: | 2019-04-23 16:13:30 |
Message-ID: | CAF4RT5Q3LMAaphQoWaqUQCLw9ABSbXBeBbFN+Pp89+dszN7+vA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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);
From | Date | Subject | |
---|---|---|---|
Next Message | David Raymond | 2019-04-23 16:33:14 | RE: SELECTing for group membership of many groups? |
Previous Message | Pól Ua Laoínecháin | 2019-04-22 20:07:13 | Re: Want records to be UNIQUE. When searching for dupes, stop on first matching record. |