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