CODEWITHSUNDEEP
sqloracle-database
ahmed devil
ahmed devil

Reputation: 41

How to select from a single table with a condition across multiple rows of the same column

I have a table that includes columns for medical numbers and procedures. There are a lot of rows and medical numbers can be repeated with many procedures; for example:

Mid_no    procedure
--------------------
100.          20
100.          30
200.          30

I want to select all mid_no that have procedure 30 and do not have procedure 20. In the above example, the desired result would be:

Mid_no.    Procedure
---------------------
200.        30

Upvotes: 3

Views: 71

Answers (3)

Aleksej
Aleksej

Reputation: 22949

With a single scan of your table, you can use:

select distinct Mid_no
from (
        select count(case when procedure=20 then 1 end ) over ( partition by Mid_no) as has20,
               count(case when procedure=30 then 1 end ) over ( partition by Mid_no) as has30,
               Mid_no
        from yourTable
     )
where has20 = 0 
  and has30 != 0

The internal query counts the number of occurrences of 20 and 30 in two different values, for each Mid_no; the external one simply picks the only records with no occurrence of 20 and at least one occurrenxe of 30.

Upvotes: 0

Kacper
Kacper

Reputation: 4818

select mid_no from table where procedure = 30
intersect
select mid_no from table where procedure != 20

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

SELECT t.mid_no,
  t.procedure
FROM TABLE t
WHERE NOT EXISTS
  (SELECT 1 FROM TABLE t1 WHERE t1.mid_no = t.mid_no AND t1.procedure = 20
  )
AND t.procedure = 30;

Upvotes: 3

Related Questions