Is this query possible?

[evanscamman]

I have a table that shows links between items in an ordering database:

SourceItem TargetItem
1000 1001
1001 1000
1001 1005
1005 1001

Item 1005 is not directly linked to item 1000.
Is it possible to return all 3 items with a query?
Some type of union query perhaps?

The link allows substitution of one item for another.

In other words:
A = B
B = C
Therefore, indirectly A = C.
Is there any way to write a query that will recognize this relationship?

Thank you,
Evan

 

[HiTechCoach]

See:

Subquery basics

Hope this helps ....

 

[Atomic Shrimp]

I think what you need is a 'self-join' query - something like this:
SELECT mytable.sourceitem, mytable_1.targetitem
FROM mytable INNER JOIN mytable AS mytable_1 ON mytable.targetitem = mytable_1.sourceitem
WHERE (((mytable_1.targetitem)<>[mytable].[sourceitem]));

-Which gives the result:
sourceitem targetitem
1005 1000
1000 1005

-It only works for one degree of separation, however - if you had one item linked to another via two or more intermediate steps, this query would not discover the entire relationship (although it would find all of the one-ste-removed subsets within that relationship).

To find relationships that span any number of intermediate steps, you'd probably have to use recursion, which I don't know much about.

It strikes me though, that your application probably ought to create records describing the link between 1000 and 1005, if it's a legitimate link. And probably doesn't need to include two rows to describe the link in each direction.

 

[evanscamman]

Mike,

I have about 6 different types of possible links between items. Most of them are one way, requiring one record. Substitution is two-way - that's why I have two records. If I were only doing substitution, I would probably use a different data structure. As you stated, a solution that finds an indirect link via a 3rd item wouldn't work if indirect link passed thru 2 or 3 items first. It seems like a simple task, but I can't figure any easy way of doing it. Perhaps this is a limitation of relational databases - I know there are many threads about this....

I have considered using a more normalized approach with a separate table for a group of linked items - assign a PK to each group. But this breaks down when you have two large groups of items, then a link is created between the groups - now they all should have the same PK...