Oracle/SQL: Recursive Query for a Many-to-many relationship

Question

I am trying to make a query in Oracle SQL that is able to find all connected records through it's parent, then find the parents of the children and so on.

I have been looking into CONNECT BY and START WITH select queries but I have not been able to find an example that fits what I'm trying to do.

I'd prefer to only have to use SQL and/or avoid using loops for the sake of speed of execution.

For Example:

Table:

Row#	ID-A	ID-B
1	ABC	123
2	BCA	123
3	CBA	123
4	CBA	321
5	CAB	321
6	BAC	213

SEARCH FOR: "BCA" Expected records: Rows 1, 2, 3, 4, and 5

*Side note: ID-B is a foreign key to another table that has ID-B as it's primary key.

If I search for "BCA" it will find all other records with the same ID-B (123). So it finds Row 1 and Row 2 but when it finds Row 3 with the record | CBA | 123 | it finds any other records with the ID-A of "CBA". Then because of that it looks for all records with the ID-B of "321" as well and so on.

I'm not sure if I explained this well enough so let me know of anything that is unclear.

Just some test code, but it's some of what I've tried:

    WITH TEST_1 (IDProd, IDRelation, Generation) AS
    (
      
      SELECT bu.bu_num
      , TO_CHAR(bu.bu_num)
      , 0
      FROM BU_TAB bu
     WHERE bu.bu_num = 12345

      UNION ALL
      
      
      SELECT T.IDPROD
      , CASE WHEN t.Generation <= 0 THEN TO_CHAR(d.bu_num)
        ELSE TO_CHAR(d.da_identifier) END
       , CASE WHEN t.Generation <= 0 THEN T.Generation - 1
        ELSE T.Generation + 1  END
      FROM TEST_1 T
      inner join DA_TAB d
      on d.bu_num = T.IDRelation
      where t.Generation <= 0
      

    )
    SELECT * 
    FROM TEST_1
    order by IDProd, Generation



  SELECT level, d.da_identifier, bu.bu_num
    FROM BU bu
       , DA_TAB d
   WHERE bu.bu_num = d.bunit_num
   START WITH bu.bu_num = 12345
   CONNECT BY PRIOR bu.bu_num = b.bu_num

user_16409126 · Answer

I think I was able to figure it out; and of course the result isn't all that impressive and it was much simpler than I would've thought while researching for hours. I still need to completely verify the results and make sure everything is as I expect, but like mathguy said in the comments: I was able to do it with a connect by and it seems to be pretty quick with my data but as far as I've seen it's only needed to extend to a path level of like 8 or so.

Here's the core of it if anyone's curious:

 SELECT DISTINCT b1.da_identifier AS daid2
               /*, b.da_identifier
               , b.bu_num
               , b1.bu_num AS bu_num2
               , CONNECT_BY_ROOT b.da_identifier
               , SYS_CONNECT_BY_PATH (b.da_identifier, '|')
               , LEVEL AS lvl*/
   FROM DA_TAB b
   JOIN DA_TAB b1 ON b1.da_identifier = b.da_identifier 
                 AND b1.bu_num <> b.bu_num --Probably not needed
  START WITH b.da_identifier = 'ABCXYZ123'
CONNECT BY NOCYCLE PRIOR b.bu_num = b1.bu_num

I wasn't able to find any examples of my exact situation so I figured I might as well post it if there's anyone who could benefit from it.

Oracle/SQL: Recursive Query for a Many-to-many relationship

1 Answers