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 <[email protected]> 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);
> 
>