5

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.

2 Answers 2

4

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

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.