Loop or Nested ???

[DugyWugy]

Hello List - I hope I'm making myself clear in this question,

I'm having a problem determining what type of a query to use for returning records from my two related tables. I'm trying to query table one for a record. The record is in table two in at most two fields. Once I retrieve the record from table two that contains the same value as was retrieved from table one I want my query to 'loop back' to table one and retrieve another record that is the same as one of the values in table two. This will go on until a condition is met (ie. Position = OFF). Here is a general outline of my tables

Table 1:
ID POSITION
1 ON
2 ON
3 ON
4 OFF
5 ON

Table 2:
ID FROM TO
001 1 2
002 2 3
003 3 4
004 4 5

Query result would return record 1 from table one. This value is found in the FROM field of table two, so the query would return record 001 from table two. I want the query to then get the value in the TO column of table two as the value to match to retrieve a record in table one and table two - record 2 in table one and record 002 in table 2 (FROM field). This 'loop' will continue returning records and populating itself until the condition of OFF is returned in the POSITION field of table one.

Please let me know if I'm not clear.
Thanks in advance,
Doug

 

[Steve101]

Doug,

Your requirement here is quite complex, and you're not going to be able to achieve a general purpose solution, that will work with any set of data using just queries. Having said that, if your levels of "nesting" were, say constrained to 4, then you might be able to do what you wanted by "chaining" the two tables to each other, and copies of each other to form an "ancestoral chain". You'd need to use appropriate join types to ensure that a result was always returned, even for a chain that didnt extent to the full set of 4 connections. I hope that this is making some form of sense.

An alternative (and preferable) solution, would be to use some form of recursive programming to traverse the "parent child" structure presented. This is typical first or second year computer science stuff, so if you are able to pal up with someone with this sort of background, they should be able to help you with this. If you're game enough to do your own research and have some programming background, look up material on "tree traversal algorithms".

I may be making this harder than it needs to be; Perhaps someone else can offer you simpler advise. I hope that this helps,
Good luck,
Steve