Re: SELECTing for group membership of many groups?

I would do the heart of the solution like this:

SELECT ug.user_id
FROM user_group ug
WHERE ug.group_id = 5

INTERSECT

SELECT ug.user_id
FROM user_group ug
WHERE ug.group_id = 6

INTERSECT

SELECT ug.user_id
FROM user_group ug
WHERE ug.group_id = 7

I’m sure there are syntax errors, but what I’m trying to convey is the concept.

The result is only the user_id’s from the rows of user_group that match all three group_id’s.

Then, in syntax I’m not showing, take those remaining user_id’s (in your example, only one of them), and join to the_user to get the user name.

Mark

> On Apr 23, 2019, at 12:13, Pól Ua Laoínecháin <linehanp(at)tcd(dot)ie> wrote:
>
> 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);
>
>