SQL Query From Query Results

Kyudos

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? (So

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).

Mycroft Holmes

My understanding is that you have only 2 levels, Assembly and child items called components. It does seem weird that you would call the component foreign key to the assembly table ComponentKey the Component table should have a primary key ComponentKey and a foreign key AssemblyKey.

Select *
From Assembly A
left join Components C on C.ComponentKey = A.AssemblyKey

This will give you all the assemblies even if there are no components.

Never underestimate the power of human stupidity RAH

Kyudos

Maybe I haven't described it well! (note I didn't design this schema - its awful, and evolved from a Fortran flat file format) The components table is the 'main' table. An assembly is type of component, consisting of a number of other components. ComponentKey is the primary key of the component table. Thus I can easily extract the list of components which are assemblies, by marrying that with the assembly table in the manner you describe. That gives me result in the form:

Description ItemKey Quantity
Assembly A 4721 2.0
Assembly A 4854 0.5
Assembly A 4719 2.0
Assembly A 4854 0.5
Assembly B 4712 1.0
Assembly B 4713 1.0

The ItemKey values incestuously refer to other components in the Components table. So I essentially want to do something like:

For Each ItemKey In QueryResult1
SELECT Description From Components WHERE ComponentKey = ItemKey
Next ItemKey

to get something like:

Description ItemKey Desc Quantity
Assembly A Component M 2.0
Assembly A Component N 0.5
Assembly A Component O 2.0
Assembly A Component N 0.5
Assembly B Component P 1.0
Assembly B Component Q 1.0

Ugh - only without the duplicates. Clear as mud?

Kyudos

I got there with:

SELECT DISTINCT Assemblies.ItemKey, Components.Description INTO Temp
FROM Assemblies, Components
WHERE Assemblies.ItemKey = Components.ComponentKey

then

SELECT DISTINCT Components.Description, Temp.Description
FROM Assemblies, Components, Temp
WHERE Components.ComponentKey = Assemblies.AssemblyKey AND Assemblies.ItemKey = Temp.ItemKey AND ((Components.Flags=1))

(Components.Flags=1 = Indicates an assembly) But can this be condensed / done without a temp table?

Mycroft Holmes

Sorry I'm not get email notifications at the moment! My eyes cross when I look at your query trying to figure out the joins. I have not used that style of join in over a decade. You can use a sub select but it makes no real difference, why do you not want to used a table variable?

Never underestimate the power of human stupidity RAH

Kyudos

Mycroft Holmes wrote:

You can use a sub select but it makes no real difference, why do you not want to used a table variable?

Crappy reasons. Our database front end program has an advanced option to execute user entered queries - it essentially only allows you one SQL statement, and I don't want to encourage users who only think they know what they are doing to start creating their own tables. And I'm especially not fgoing to let them DROP tables! :-D So I was wondering if I could get this done in one SELECT statement.

Mycroft Holmes

Kyudos wrote:

Crappy reasons

No worse than some I have heard! Try this, uses join and a sub select.

SELECT DISTINCT
Components.Description,
Temp.Description
FROM Assemblies
INNER JOIN Components ON Components.ComponentKey = Assemblies.AssemblyKey
AND Components.Flags = 1
INNER JOIN (SELECT DISTINCT Assemblies.ItemKey,Components.Description
FROM Assemblies
INNER JOIN Components on Assemblies.ItemKey = Components.ComponentKey) T
ON Assemblies.ItemKey = T.ItemKey

Never underestimate the power of human stupidity RAH