Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to find the unmatched records where only partial records matches the conditions?

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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.)

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading