I'm trying to select the rows for MEDs (type) and the related child rows for ORAL (type) where each MED type has multiple ORAL types from the following table.

This should return rows with ID 12, 13, and 15. I've tried the following using a CTE to my multiple counts of ORAL rows. Then tried joining that to pull the three rows I need, but it doesn't return the way I think it should. It's returning the IDs but only in two rows.

WITH COUNT_CTE (ID, CNT)
AS
(
    SELECT A.ID, COUNT(A.ID) AS CNT
    FROM Table1 A
    JOIN Table1 B
    ON A.ID = B.PARENT_ID 
    WHERE A.TYPE = 'MED' and B.TYPE = 'ORAL'
    GROUP BY A.ID
)

SELECT A.*, B.*
FROM Table1 A
JOIN Table1 B
ON A.ID = B.ID
JOIN COUNT_CTE C
ON A.ID = C.ID
WHERE A.TYPE = 'MED' AND B.TYPE = 'ORAL' AND C.CNT > 1

This returned:

I'm not sure if a CTE is even needed or if the group by could be done inside a subquery. Either way I'm not sure how to separate the children into their own rows to look like the below correct return.

The return should be:

CodePudding user response：

You can do this in three steps. Count the children, like you are. Then join on only rows with multiple children and child or parent rows.

-- Get a count of how many ORAL rows each MED parent has.
with child_count as (
  SELECT A.id, COUNT(A.ID) AS CNT
  FROM Table1 A
  JOIN Table1 B
  ON A.ID = B.PARENT_ID 
  WHERE A.TYPE = 'MED' and B.TYPE = 'ORAL'
  GROUP BY A.ID
)
select table1.*
from child_count cc
join table1
  -- Filter for only rows with a parent with more than one child.
  on cc.cnt > 1
  -- And only child ORAL rows, or parent rows.
  and (
    (cc.id = table1.parent_id and table1.type = 'ORAL')
    or cc.id = table1.id
  )

Demonstration.

CodePudding user response：

Many solutions, here's one using using INTERSECT within a CTE.

with target_meds as (
  select id
  from my_table
  where type = 'MED'
  intersect
  select parent_id
  from my_table
  where type = 'ORAL'
  group by 1
  having count(id) > 1
 )
select m.*
from my_table m
join target_meds t
  on coalesce(m.parent_id, m.id) = t.id
where m.type in ('MED', 'ORAL');

View on DB Fiddle