twisted query

[Corticus]

Hi all,

I've got a twisted question for you.
I made a query that does what I need, but its really slow, so there must be a better way.
I'm interested in 2 columns of data. One is a part number, and one is a component number. You might see data like this:

Part
10A
10A
10A
10B
10B
1034
1034
1000A01

Component
1034
1035
1036
A5454
A5454
1000A01
1000A02
L16545


I need a list of every part for a 10A, which would be:

Part
1034
1035
1036
1000A01
1000A02
L16545

As you can see, the trick is that a component can also be a part, which can have more components, and those components also can have components...

My nasty solution for the example would implement SQL as follows:

part I

QryParts

this makes a list of all the parts and relationships
first it gets filtered by a combo so it just shows one Part

SELECT
FROM ((TblItem LEFT JOIN TblItem AS TblItem_1 ON TblItem.Component = TblItem_1.Part) LEFT JOIN TblItem AS TblItem_2 ON TblItem_1.Component = TblItem_2.Part) LEFT JOIN TblItem AS TblItem_3 ON TblItem_2.Component = TblItem_3.Part;

Ugh...

part II

this gives the actual list of components for the part

SELECT DISTINCT QryParts.TblItem.Part
FROM QryParts

UNION SELECT DISTINCT QryParts.TblItem_1.Part
FROM QryParts;

UNION SELECT DISTINCT QryParts.TblItem_2.Part
FROM QryParts

UNION SELECT DISTINCT QryParts.TblItem_3.Component
FROM QryParts;

I just don't understand why its slow.....

Thanks for any help,
Corticus

 

[Corticus]

Thanks for your reply,

I'm getting disheartened that there isn't a better way to do this. Unfortunately, there isn't one field that is a primary key, it would be a combination of both the part and component field. There is only one table of interest.
I agree that indexing would offer an improvement, though I'm not exactly sure how to implement it. I was thinking I might run an update query to add an index to this table, one index for the part, and one for the component, and then I won't have to pull the actual part and component IDs until the last stage of the query.

Any ideas would be greatly appreciated, I've got this question all over the place and its a real pickle.

Thanks again,
Corticus

 

[Corticus]

one solution, more to come

Okay,

Here's one considerably faster solution all in one SQL expression:
SELECT A.Component
FROM TblItem AS A
WHERE A.Part=[ENTER PART]
OR A.Component In (select B.Component from TblItem as B where B.Part in (SELECT C.Component FROM TblItem as C WHERE C.Part=[ENTER PART]))
OR A.COMPONENT IN (SELECT B.COMPONENT FROM TBLITEM AS B WHERE B.PART IN (SELECT C.COMPONENT FROM TBLITEM AS C WHERE C.PART IN(SELECT D.COMPONENT FROM TBLITEM AS D WHERE D.PART=[ENTER PART])));

Nice, eh?

btw, this problem is called
'transitive closure'

Thanks!
Corticus