SQL to select all siblings with specific child

Question

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

| 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:

| 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:

| 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:

| 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? That is, for a given part, return the assemblies it belongs to, along with all the other parts in the assembly.

I'm using MySQL, and here's an sqlfiddle if it helps.

Answer 1

In the WHERE you can simply filter for the parts which have an assemblie id equal to the assemblie id of 'Part B'

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'
  );

Answer 2

I think this should give the results needed:

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)