Conditional query to request analysis of employee training

Question

I need to run training queries that return results for questions such as: "Who completed this training, but not this training?"

In the simplified table below, I would like to know which employee completed training_id 1 (as indicated in the date in the complete_date field), but did not finish training_id 7.

+-------------+-------------+----------------+
| emp_id      | training_id | completed_date |
+-------------+-------------+----------------+
| 1           | 1           | 2010-04-02     |
+-------------+-------------+----------------+
| 1           | 7           | Null           |
+-------------+-------------+----------------+
| 2           | 1           | Null           |
+-------------+-------------+----------------+
| 2           | 7           | Null           |
+-------------+-------------+----------------+

The desired result would be emp_id 1, and we would like to return its completed training and incomplete training on query parameters:

+-------------+-------------+----------------+
| emp_id      | training_id | completed_date |
+-------------+-------------+----------------+
| 1           | 1           | 2010-04-02     |
+-------------+-------------+----------------+
| 1           | 7           | Null           |
+-------------+-------------+----------------+

I can't figure out how to do this with a regular request, because it seems like IF logic is needed. Example: Return the rows where this training is completed, and return the rows where this second training will not be complete, BUT ONLY if the first training is completed.

How can I express something like this in SQL?

Answer 1

EXISTS

SELECT t.*
FROM training t

# which employee has completed training_id 1
WHERE t.training_id = 1 and t.completed_date is not null

#but has not finished training_id 7.
AND NOT EXISTS (
     SELECT * FROM training t2
     where t2.emp_id=t.emp_id
       and t2.training_id = 7
       and t2.completed_date is not null)

- , completed (4,5,6) but not (1,9), counts:

SELECT t.emp_id
FROM training t
WHERE t.training_id in (4,5,6) and t.completed_date is not null
group by t.emp_id
having count(distinct emp_id) = 3

AND NOT EXISTS (
     SELECT * FROM training t2
     where t2.emp_id=t.emp_id
       and t2.training_id in (1,9)
       and t2.completed_date is not null)

, ,

SELECT e.*
FROM
(
    SELECT t.emp_id
    FROM training t
    WHERE t.training_id in (4,5,6) and t.completed_date is not null
    group by t.emp_id
    having count(distinct emp_id) = 3

    AND NOT EXISTS (
         SELECT * FROM training t2
         where t2.emp_id=t.emp_id
           and t2.training_id in (1,9)
           and t2.completed_date is not null)
) search
inner join training e on e.emp_id = search.emp_id
order by e.emp_id

Answer 2

select * from training where emp_id in
(
  select distinct emp_id from training where completed_Date is not null
)

Answer 3

You can make a self-join in this table ("pretend these are two identical tables and join them"):

SELECT t1.emp_id, t1.training_id, t1.completed_date,
       t2.training_id, t2.completed_date
    FROM training AS t1 /* the aliases are needed for the self-join */
    JOIN training AS t2
        ON t1.emp_id = t2.emp_id
        AND t2.training_id = 7 /* second training  ID */
    WHERE t1.training_id = 1 /* first training ID */

This should give you this result:

t1.emp_id | t1.training_id | t1.completed_date | t2.training_id | t2.completed_date
 1            1               2010-04-02          7                NULL
 2            1               NULL                7                NULL

Then you can further restrict the WHERE query, for example. with

         AND t1.completed_date IS NOT NULL
         AND t2.completed_date IS NULL

which will give you the kit you want - training 1 is completed, training 7 is not.