1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL to select all siblings with specific child

Discussion in 'General PHP and MySQL Discussions' started by Myke623, Dec 6, 2014.

  1. Myke623

    Myke623 Active Member

    I have a simple parent-child hierarchical arrangement using two tables: Assemblies (parents) and Parts (children). An assembly consists of one or more parts.

    Code:
    | AID |     A_NAME |
    |-----|------------|
    |   1 | Assembly X |
    |   2 | Assembly Y |
    |   3 | Assembly Z |
    
    | PID | P_NAME | AID |
    |-----|--------|-----|
    |   1 | Part A |   1 |
    |   2 | Part B |   1 |
    |   3 | Part C |   1 |
    |   4 | Part D |   1 |
    |   5 | Part A |   2 |
    |   6 | Part B |   2 |
    |   7 | Part C |   3 |
    |   8 | Part D |   3 |
    

    To show the complete list of Assemblies with their Parts, I can run the following SQL query:

    SELECT a.*, p.*
    FROM assemblies a
    LEFT JOIN parts p USING (AID);

    and it returns:

    Code:
    | AID |     A_NAME | PID | P_NAME |
    |-----|------------|-----|--------|
    |   1 | Assembly X |   1 | Part A |
    |   1 | Assembly X |   2 | Part B |
    |   1 | Assembly X |   3 | Part C |
    |   1 | Assembly X |   4 | Part D |
    |   2 | Assembly Y |   5 | Part A |
    |   2 | Assembly Y |   6 | Part B |
    |   3 | Assembly Z |   7 | Part C |
    |   3 | Assembly Z |   8 | Part D |
    
    Now, if I only want to return Assemblies that include a specific Part (say 'Part B'), then I could run this query:

    SELECT a.*, p.*
    FROM assemblies a
    LEFT JOIN parts p USING (AID)
    WHERE p.P_NAME='Part B';

    which returns:

    Code:
    | AID |     A_NAME | PID | P_NAME |
    |-----|------------|-----|--------|
    |   1 | Assembly X |   2 | Part B |
    |   2 | Assembly Y |   6 | Part B |
    
    However, what I'd really like to see is all the sibling parts* as well:

    Code:
    | AID |     A_NAME | PID | P_NAME |
    |-----|------------|-----|--------|
    |   1 | Assembly X |   1 | Part A |*
    |   1 | Assembly X |   2 | Part B |
    |   1 | Assembly X |   3 | Part C |*
    |   1 | Assembly X |   4 | Part D |*
    |   2 | Assembly Y |   5 | Part A |*
    |   2 | Assembly Y |   6 | Part B |
    
    Any ideas on how I can achieve this? Here's an sqlfiddle if it helps.
     
  2. Myke623

    Myke623 Active Member

    In case someone comes looking in future, I received a couple of helpful answers from stackexchange:

    Code:
    SELECT a2.*, p2.*
    FROM (
    SELECT a.* 
    FROM assemblies a
    LEFT JOIN parts p USING (AID)
    WHERE p.P_NAME='Part B') AS a2
    LEFT JOIN parts p2 USING (AID)
    
    and:
    Code:
    SELECT a.*, p.*
    FROM assemblies a
    LEFT JOIN parts p USING (AID)
    WHERE p.AID IN
      (
        SELECT AID FROM parts WHERE P_NAME='Part B'
      );
     

Share This Page