Db2 for z/OS & Db2ZAI

 View Only
  • 1.  How to Retrieve Child Table's "Siblings"

    Posted Fri November 25, 2022 09:33 AM
    I have two tables, call them 'Parent' and 'Child.' The primary key of Parent (columns P1, P2, and P3) is foreign key on Child. Child's primary key is (P1, P2, P3, and C1). 

    Given a value for C1, how can I query all of the other rows on the Child table with the same Parent?

    ------------------------------
    Curt Gilker
    ------------------------------

    #Db2forz/OS


  • 2.  RE: How to Retrieve Child Table's "Siblings"

    Posted Mon November 28, 2022 04:38 AM

    If I am seeing it right your siblings have the same parent. You could do a join like this

    SELECT B.C1
    FROM CHILD A,
         CHILD B
    WHERE A.P1 = B.P1
      AND A.P2 = B.P2
      AND A.P3 = B.P3
      AND A.C1 = <your identified child>;


    Additionally, recursive SQL often is a good idea to determine hierarchical / forein key relationships across tables. Not required here, but perhaps you have more requirements. Examples: BOM of course, but also products to top-categories (via sub, sub-sub etc. categories) and many more. Lots of examples exist on the web.



    ------------------------------
    Christian Lenke
    ------------------------------



  • 3.  RE: How to Retrieve Child Table's "Siblings"

    Posted Tue November 29, 2022 09:50 AM
    And include the exclusion of self:
    SELECT S.C1
      FROM CHILD C
         , CHILD S
      WHERE C.C1 = :HV
        AND S.P1 = C.P1
        AND S.P2 = C.P2
        AND S.P3 = C.P3
        AND S.C1 <> C.C1
    ​


    ------------------------------
    Bruce Williamson
    ------------------------------



  • 4.  RE: How to Retrieve Child Table's "Siblings"

    Posted Tue November 29, 2022 04:34 PM
    Hi Christian

    Shouldn't we be joining on the PARENT and CHILD like this?
    SELECT B.C1
    FROM PARENT A,
               CHILD B
    WHERE A.P1 = B.P1
    AND A.P2 = B.P2
    AND A.P3 = B.P3
    AND A.C1 = your value of C1;

    ------------------------------
    Rupali Wagle
    ------------------------------



  • 5.  RE: How to Retrieve Child Table's "Siblings"

    Posted Tue November 29, 2022 04:34 PM
    Shouldn't the parent table be included?
    SELECT B.C1
    FROM PARENT A,
    CHILD B
    WHERE A.P1 = B.P1
    AND A.P2 = B.P2
    AND A.P3 = B.P3
    AND A.C1 = your value of C1;

    ------------------------------
    Rupali Wagle (she/her/hers)
    Principal Technical Support Engineer
    Rocket Software, USA
    T: +1 781 577 4493 (W) +1 713 444 2877 (C)
    Support: +1 855 577 4323
    E: rwagle@rocketsoftware.com
    W:RocketSoftware.com
    ------------------------------



  • 6.  RE: How to Retrieve Child Table's "Siblings"

    Posted Sun December 04, 2022 08:40 PM

    Column C1 doesn't exist in the PARENT table

    In answer to your question, no the PARENT table is not required as you are only interested in siblings and all the detail needed exists in the CHILD table



    ------------------------------
    Bruce Williamson
    ------------------------------



  • 7.  RE: How to Retrieve Child Table's "Siblings"

    Posted Tue November 29, 2022 09:35 AM
    A small amendment to what Christian has written, I'm not sure you require the parent table Curt, try the following:
    SELECT S.C1
      FROM CHILD C
         , CHILD S
      WHERE C.C1 = :HV
        AND S.P1 = C.P1
        AND S.P2 = C.P2
        AND S.P3 = C.P3
        AND S.C1 ^= C.C1
    ​


    ------------------------------
    Bruce Williamson
    ------------------------------