I have a table table_A
ID EMP
1 9999
1 1
2 9999
2 2
2 3
3 9999
3 9999
3 4
3 4
3 4
4 9999
4 9999
4 9999
5 5
5 6
I want the records where where emp <> 9999 but this case should be satisfied not for all the id. So my expected output will be
id emp
1 1
2 2
2 3
3 4
3 4
3 4
Here records with id 4 and 5 are not present because their distinct emp values are either 9999 only or any other values rather than 9999
I tried but not getting the desired results:
SELECT ID, EMP
FROM table_a
WHERE ID IN (
SELECT ID
FROM table_a
WHERE EMP <> 9999
GROUP BY ID
HAVING COUNT(DISTINCT CASE WHEN EMP <> 9999 THEN EMP END) > 0
)
AND EMP <> 9999
>Solution :
You want to show all rows where emp is not 9999 and exists a row for the same id with emp = 9999.
select id, emp
from table_a
where emp <> 9999
and id in (select id from table_a where emp = 9999)
order by id, emp;
(Of course, you can use an EXISTS clause instead of the IN clause, if you like that better.)