SQL query to get correct data

user_14853311 0
Viewed 86

I want to get TABLE2 data from TABLE1 data.

If flg is 1, cusId1 and cusId2 are same person.

For example, If 1 & 2 are same person and 2 & 3 are same person, 1 & 3 are same person.

TABLE1

cusId1 cusId2 flg
1 2 1
2 3 1
3 4 2
5 1 2
6 7 1
7 8 1
8 6 1

TABLE2

cusId mainId
1 1
2 1
3 1
4 4
5 5
6 6
7 6
8 6

I've been thinking about what kind of SQL to use, but I can't think of it.
Should I use recursive SQL?
The database type is Oracle12c.


WITH VREC (TABLE1 , cusId1  , cusId2) AS
(
   SELECT cusId1 || ' -> '|| cusId2 , cusId1, cusId2 FROM TABLE1
   WHERE cusId1 = '1' AND cusId2 = '2'
UNION ALL
   SELECT V.TABLE1 || ' -> '|| K.cusId2 , K.cusId1, K.cusId2 FROM TABLE1 K , VREC V
   WHERE V.cusId1 = K.cusId1
   AND flg = '1'
)
SELECT TABLE1 FROM VREC

# TABLE1
# 1 -> 1
# 1 -> 2 -> 3

Please let me know if you have a good idea.

oracle sql
1 Answers

This is rather tricky because you a have a cyclic graph. You can handle it using a recursive CTE and keeping track of the nodes that have been visited:

with alledges as (
      select cusid1, cusid2
      from t
      where flg = 1
      union all
      select cusid2, cusid1
      from t
      where flg = 1
      union all
      (select cusid1, cusid1
       from t
       where not exists (select 1 from t t2 where t2.flg = 1 and t.cusid1 in (t2.cusid1, t2.cusid2))
       union
       select cusid2, cusid2
       from t
       where not exists (select 1 from t t2 where t2.flg = 1 and t.cusid2 in (t2.cusid1, t2.cusid2))
      )
     ),
     cte (parentid, childid, visited) as (
      select distinct cusid1, cusid1, ',' || cusid1 || ','
      from alledges
      union all
      select cte.parentid, ae.cusid2, cte.visited || ae.cusid2 || ','
      from cte join
           alledges ae
           on cte.childid = ae.cusid1
      where cte.visited not like '%,' || ae.cusid2 || ',%'
     )
select childid, min(parentid) 
from cte
group by childid
order by childid;

Here is a db<>fiddle.

user_11440350
Related