Self Join Query - Display all records

  • Thread starter Thread starter Char
  • Start date Start date
C

Char

Hello,
I have a created an inventory database that uses a self
join since all records have virtually the same types of
information.

Here's an example (records are made up):

ID Foreign_Key Part Name Serial #
1 Computer 1 12345
2 1 Hard Drive 67890
3 2 Read/Write Head 87654

I want to be able to query for Computer 1 and have every
part (both the Hard Drive and the Read/Write Head)
returned in my query.


Thanks in advance!
 
First, I assume your Foreign_Key value doesn't contain
a '2', but rather is a '1'. i.e.:

tblTable
ID Foreign_Key Part Name Serial #
1 Computer 1 12345
2 1 Hard Drive 67890
3 1 Read/Write Head 87654

I want to be able to query for Computer 1 and have every
part (both the Hard Drive and the Read/Write Head)
returned in my query.
Click to expand...

Try a query such as this one (air query):

Select * From tblTable Where [Foreign_Key]=1


--- Did I miss something?

David Atkins, MCP
 
I see now, the read/write head is part of the hard drive,
which in turn is part of computer 1.
ID Foreign_Key Part Name Serial #
1 Computer 1 12345
2 1 Hard Drive 67890
3 2 Read/Write Head 87654
Click to expand...

There are various ways of performing this. There was a
great article on this on SQLTeam.com. Found it:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15499

Read the topic listed here:

http://www.sqlteam.com/item.asp?ItemID=8866

It will solve your needs, discuss your options, and
generally overload you with information you probably
didn't care about.

David Atkins, MCP
 
Dear anon:

While the query cannot be written recursively, a UNION query can be
constructed that implements what you have described. However, it
would not be possible to do this so as to allow for infinite
recursion.

There is another unrelated question that works into a single coherent
solution. That is, you need to prevent cycles.

A good way to do this is to define the "levels" of the tree structure.
Each level should be numbered, and when creating a self reference,
each reference should be constrained to be a reference to a "lower"
level number. This constraint is actually a necessary and sufficient
rule to prevent cycles, without which you cannot traverse this
structure whether recursive or not.

The next question will be whether you can "skip levels" in the
references. If references are allowed only to the next "lower" level,
then skipping levels is not permitted. Evaluating questions like this
helps you define an understand the structure with which you are
working.

If, when you are finished with this, you have only 3 or 4 levels, then
the result will probably be pretty reasonable for the query work that
follows.

If you can try to work out these preliminary details of what you want,
we can begin to procede from there to get you the query work you will
need.

Hello,
I have a created an inventory database that uses a self
join since all records have virtually the same types of
information.

Here's an example (records are made up):

ID Foreign_Key Part Name Serial #
1 Computer 1 12345
2 1 Hard Drive 67890
3 2 Read/Write Head 87654

I want to be able to query for Computer 1 and have every
part (both the Hard Drive and the Read/Write Head)
returned in my query.


Thanks in advance!
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top