|As always my SQL skills are limited and rusty. How does one accomplish this?
I have a 'Components' table. A 'Component' may be an assembly of other components. This is defined in the 'Assemblies' table.
Assemblies has columns : AssemblyKey / ItemKey / Quantity.
AssemblyKey identifies with
Components.ComponentKey and is the ID of the assembly component.
ItemKey is the ComponentKey ID of the constituent component of the assembly
Quantity is the amount of ItemKey items in the assembly.
I want to list all the assemblies in the database, with all the components in each assembly.
Can I do that in one query?
SELECT Components.Description, Assemblies.ItemKey FROM Assemblies, Components
WHERE Assemblies.`Assembly Key` = Components.`Component Key`
Gives me the assembly description and the IDs of the constituent components. I want to turn that resulting list of ItemKeys into descriptions from the Component table).