search pk have a AND b

Question

How can I achieve something like: "Any employee with Present AND Latein status"

My tables is like this

employee
+--------+---------+
| emp_id | name    |
+--------+---------+
| a001   | michael |
+--------+---------+
| a002   | john    |
+--------+---------+

status
+--------+---------+
| emp_id | status  |
+--------+---------+
| a001   | Present |
+--------+---------+
| a001   | Latein  |
+--------+---------+
| a002   | Absence |
+--------+---------+

Possible duplicate of [SQL Server - select rows that match all items in a list](http://stackoverflow.com/questions/15977126/sql-server-select-rows-that-match-all-items-in-a-list) — Tab Alleman, Jun 27 '16 at 14:35

score 3 · Accepted Answer · answered Jun 27 '16 at 14:05

3

select e.name
from employee e
join status s on s.emp_id = e.emp_id
where s.status in ('Present', 'Latein')
group by e.emp_id, e.name
having count(*) = 2

answered Jun 27 '16 at 14:05

juergen d

201,996
37
293
362

score 2 · Answer 2 · answered Jun 27 '16 at 14:05

One way is to use an inner join on a derived table from status:

SELECT name
FROM employee E
INNER JOIN
(
    SELECT emp_Id
    FROM status 
    WHERE status IN('Present', 'Latein')
    GROUP BY emp_Id
    HAVING COUNT(*) = 2
) D 
ON E.emp_Id = D.emp_Id

The derived table will include only those employees that have records with both Present and Latein.

score 2 · Answer 3 · answered Jun 27 '16 at 14:05

Use condtional aggregation on the status table to determine which employees have 'Present' and 'Latein'. Then join this result to the employee table to get the matching names.

SELECT t1.emp_id, t1.name
FROM employee t1
INNER JOIN
(
    SELECT emp_id,
        SUM(CASE WHEN status = 'Present' OR status = 'Latein'
                 THEN 1 ELSE 0 END) AS statusSum
    FROM status
    GROUP BY emp_id
    HAVING statusSum = 2
) t2
    ON t1.emp_id = t2.emp_id

score 1 · Answer 4 · answered Jun 27 '16 at 14:05

1

I think that you are looking for Latein employees. If they are late, they must be present, otherwise they did not come and are Absent. So for you it is best to use

SELECT emp_id FROM status WHERE status='Latein'

answered Jun 27 '16 at 14:05

Hynek Bernard

744
1
11
30

search pk have a AND b

4 Answers4