SQL to Select only full groups of data-CodePudding

Let's say I have three sample tables for groups of people as shown below.

Table users:

id	name	available
1	John	true
2	Nick	true
3	Sam	false

Table groups:

id	name
1	study
2	games

Table group_users:

group_id	user_id	role
1	1	teach
1	2	stdnt
1	3	stdnt
2	1	tank
2	2	heal

And I need to show to a user all groups that he participates in and also available right now, which means all users in that group have users.available = true.

I tried something like:

SELECT `groups`.*, `users`.* , `group_users`.*
FROM `groups` 
LEFT JOIN `group_users` ON `groups`.`id` = `group_users`.`group_id` 
LEFT JOIN `users` ON `users`.`id` = `group_users`.`user_id` 
WHERE `users`.`available` = true AND `users`.`id` = 1

But it just shows groups and part of their users, that are available. And I need to have ONLY the groups that have all their users available.

If I were to find all available groups as User 1 - I should get only group 2 and it's users. How to do this the right way?

Tables DDL:

CREATE TABLE users ( 
    id        int          PRIMARY KEY, 
    name      varchar(256) NOT NULL, 
    available bool 
); 
CREATE TABLE teams ( 
    id        int          PRIMARY KEY, 
    name      varchar(256) NOT NULL 
); 
CREATE TABLE team_users ( 
    team_id   int          NOT NULL, 
    user_id   int          NOT NULL, 
    role      varchar(64) 
); 

INSERT INTO users VALUES 
(1, 'John', true ), 
(2, 'Nick', true ), 
(3, 'Sam' , false); 
INSERT INTO teams VALUES 
(1, 'study'), 
(2, 'games'); 
INSERT INTO team_users VALUES 
(1, 1, 'teach'),
(1, 2, 'stdnt'),
(1, 3, 'stdnt'),
(2, 1, 'tank' ),
(2, 2, 'heal' );

mySQL select version() output:

10.8.3-MariaDB-1:10.8.3 maria~jammy

CodePudding user response：

Check do you need in this:

WITH cte AS (
    SELECT users.name username, 
           teams.id teamid,
           teams.name teamname,
           SUM(NOT users.available) OVER (PARTITION BY teams.id) non_availabe_present,
           SUM(users.name = @user_name) OVER (PARTITION BY teams.id) needed_user_present
    FROM team_users
    JOIN users ON team_users.user_id = users.id
    JOIN teams ON team_users.team_id = teams.id
)
SELECT username, teamid, teamname
FROM cte
WHERE needed_user_present
  AND NOT non_availabe_present;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=605cf10d147fd904fb2d4a6cd5968302

PS. I use user name as a criteria, you may edit and use user's identifier, of course.

CodePudding user response：

Join the tables and aggregate with the conditions in the HAVING clause:

SELECT t.id, t.name
FROM teams t
INNER JOIN team_users tu ON t.id = tu.team_id  
INNER JOIN users u ON u.id = tu.user_id 
GROUP BY t.id
HAVING MIN(u.available) AND SUM(u.id = 1);

The HAVING clause is a simplification of:

HAVING MIN(u.available) = true AND SUM(u.id = 1) > 0

See the demo.