SQL to select all siblings with specific child

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 sq

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

StackExchange Database Administrators Q#84508, answer score: 4