How to select rows from a table while using CTE and possibly a subquery

Kosh_One 0
Viewed 32

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.

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

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:

ID PARENT_ID TYPE ID PARENT_ID TYPE
12 NULL MED 13 12 ORAL
12 NULL MED 15 12 ORAL

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:

ID PARENT_ID TYPE
12 NULL MED
13 12 ORAL
15 12 ORAL
sql postgresql
2 Answers

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.

Schwern0

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');
id parent_id type
12 null MED
13 12 ORAL
15 12 ORAL

View on DB Fiddle

Isolated0
Related