2

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
Dave G
  • 21
  • 2
  • What did you try so far? – Etienne Jul 08 '21 at 18:10
  • Posting the actual SQL you have tried would be helpful. – pmdba Jul 08 '21 at 18:11
  • I've tried to use this example http://sqlfiddle.com/#!18/319431/4 Along with queries using CONNECT BY and START WITH – Dave G Jul 08 '21 at 18:13
  • Your fiddle is on SQL server your tags say oracle. I'm assuming oracle since you mentioned connect by prior... – xQbert Jul 08 '21 at 18:14
  • I'll post my code, I just need to sanitize it quickly – Dave G Jul 08 '21 at 18:14
  • The code that I posted at this point is a mish mash of various things I've tried over the past several hours so a lot was deleted and rewritten but this is what remains. Probably won't be very helpful however. Sorry – Dave G Jul 08 '21 at 18:28
  • Your problem is an instance of the general problem of finding the connected components of an undirected graph (in the special case of a bi-partite graph). While it can be solved with `connect by` in SQL, if your data is of non-trivial size SQL will blow up. This is a type of problem that is much better suited for procedural processing, including PL/SQL solutions. Note that, apparently, you are using "PL/SQL" incorrectly; PL/SQL is a language similar to C or Java, it is not a nickname for Oracle SQL. –  Jul 08 '21 at 18:40
  • Thanks for the info, you gave me some new things to search for. Also sorry about the confusion, I'm doing this in PL/SQLDeveloper so I have kinda been using the terms for PL/SQL and Oracle sql interchangeably. I've made edits to get rid of the mentions to pl/sql. – Dave G Jul 08 '21 at 18:45
  • I wrote a PL/SQL solution (for graphs in general - not specialized for bi-partite graphs) on the Oracle discussion board - whatever they call it now. Unfortunately, formatted code is now impossible to read (but it can be copied and pasted) after they changed the platform provider in September 2020. There is also a SQL and a simpler (but slower) PL/SQL solution in the same thread. If interested, here's a link: https://community.oracle.com/tech/developers/discussion/4344746/les-amis-de-mes-amis-sont-mes-amis#latest –  Jul 08 '21 at 19:02
  • Thanks a lot, I'm looking into it now! – Dave G Jul 08 '21 at 19:35

1 Answers1

0

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.

Dave G
  • 21
  • 2