CODEWITHSUNDEEP
mysqlsql
zonked.zonda
zonked.zonda

Reputation: 347

MYSQL:SQL query to return a row only if all the rows satisfy a condition

Consider three tables -

  users

    id      |   type
 -----------|------------
    1       |   a
    2       |   b   
    3       |   c

 types  

    id      |   type
 -----------|------------
    a       |   X
    a       |   Y   
    b       |   X
    c       |   X
    c       |   Y
    c       |   Z

 training_status

    id      |   training|   status
 -----------|-----------|-------------
    1       |   X       |   F
    2       |   X       |   S
    2       |   Y       |   S
    3       |   X       |   F
    3       |   Y       |   S

Each user has a type, and types defines the trainings that each user of a particular type have to complete.

training_status contains status of all the trainings that a user has taken and its result (S,F). It a user is yet to take a training, there won't be any row for that training.

I would like to find out all users that have successfully completed all the trainings that they have to take.

Here's the direction that I am thinking in:

select
  id
from users
  join types
    using (type)
  left join training_status
    using (id,type)
where status NOT IN(None, F);

Obviously this is not the right query because even if the user has completed one of the trainings, we get that row. In the aforementioned example, I'd like to get id = 2 because he has completed both trainings of its type.

Upvotes: 7

Views: 10257

Answers (4)

mvsagar
mvsagar

Reputation: 2079

This should work:

SELECT id 
FROM users
WHERE id not in (SELECT x.id FROM (SELECT u.id AS id, t.type AS type, 'S' AS status
                FROM users u, types t
                WHERE u.type = t.id
                EXCEPT
                SELECT id, training, status
                FROM training_status
                                  ) AS x (id, type, status)
        )

Note that it uses set difference operator EXCEPT which gives difference of rows of all possible users and their successful training combinations and current training status. If the current status is complete for all users, the difference should yield no rows. Non zero result means there are users who did not complete required training. The outermost select gives list of users who are not in the list of users who did not complete the training, i.e, list of users who completed!

When executed in MS SQL server for your data it gives answer 2 who is the only one who completed training successfully.

Here is MySQL Version:

SELECT id 
FROM users
WHERE id not in (SELECT x.id 
             FROM (SELECT u.id AS id, t.type AS type, 'S' AS status
                   FROM users u, types t
                   WHERE u.type = t.id 
                   ) x  
             WHERE NOT EXISTS
                    (SELECT ts.id, ts.training, ts.status
                     FROM training_status ts
                     WHERE ts.id = x.id AND 
                           ts.training = x.type AND
                           ts.status = x.status
                     )
            )

+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Upvotes: 0

peterm
peterm

Reputation: 92785

Try

SELECT DISTINCT u.id
  FROM users u JOIN types t
    ON u.type = t.type LEFT JOIN training_status s
    ON u.id = s.id AND t.training = s.training
 WHERE s.status IS NOT NULL 
 GROUP BY u.id
HAVING COUNT(t.type) = SUM(CASE WHEN s.status = 'S' THEN 1 ELSE 0 END)

or

SELECT DISTINCT u.id
  FROM users u JOIN types t
    ON u.type = t.type LEFT JOIN training_status s
    ON u.id = s.id AND t.training = s.training
 GROUP BY u.id
HAVING MAX(s.status IS NULL OR s.status = 'F') = 0

Output:

+------+
| id   |
+------+
|    2 |
+------+

Here is SQLFiddle demo

Upvotes: 8

Nitu Bansal
Nitu Bansal

Reputation: 3856

Try this

select distinct
  u.id
from users u
  left join types t
    on u.type = t.type
  left join training_status ts
    on ts.training = t.training
where ts.status is not null
    and ts.status != 'F'

Upvotes: 0

Chamal
Chamal

Reputation: 1449

Try this

SELECT *
FROM users
WHERE id NOT IN (
SELECT u.id
  FROM users u 
  JOIN types t 
    ON u.type = t.type 
  LEFT JOIN training_status s
    ON u.id = s.id AND t.training = s.training
 WHERE s.status IS NULL OR s.status = 'F')

Upvotes: 0

Related Questions