How to filter out data when using same column from same table-CodePudding

I'm trying to find how many MED types have a child ORAL type that do NOT have a child IV type in the following table.

ID	PARENT_ID	TYPE
1	NULL	MED
2	1	ORAL
3	NULL	MED
4	3	ORAL
5	3	IV
6	NULL	MED
7	6	ORAL
8	NULL	MED
9	8	ORAL
12	NULL	MED
13	12	ORAL
14	12	IV
15	12	ORAL

I can find a count of All MED types that have a child ORAL type AND a child IV type with the following.

select count(distinct(a.ID))
from Table1 a
join Table1 b
on a.ID = b.PARENT_ID
join Table1 c
on a.ID = c.PARENT_ID
where a.TYPE = 'MED' and b.TYPE = 'ORAL' and c.TYPE = 'IV'

But I can't figure out how to filter out the MEDs that only have type ORAL and NOT type IV. This is what I tried but it returns nothing. It should return a count of 3 (rows 1, 6, 8).

select count(distinct(a.ID))
from Table1 a
join Table1 b
on a.ID = b.PARENT_ID
where a.TYPE = 'MED' and b.TYPE = 'ORAL'
and not exists (
    select a.ID
    from Table1 a
    join Table1 b
    on a.ID = b.PARENT_ID
    join Table1 c
    on a.ID = c.PARENT_ID
    where a.TYPE = 'MED' and b.TYPE = 'ORAL' and c.TYPE = 'IV'
)

CodePudding user response：

If I understand you correctly, then you can solve the problem like this We take all records by condition and exclude keys where IV is present

SELECT COUNT(*)
  FROM table1 t1
  JOIN table1 t2
    ON t1.id = t2.parent_id
   AND t2.utype = 'ORAL'
 WHERE t1.utype = 'MED'
   AND t1.id NOT IN (SELECT parent_id FROM table1 t3 WHERE t3.utype = 'IV')