Tough Query AnyOne?

[Fbomb]

I am challenged with trying to compare data from a row of inputs with another row of inputs for a reciprocating match. Basically, I have a row of data with the first cell being the let's say "SiteIDs" and up to 12 cells that on the same row that are affiliated to the "SiteIDs" that are called “AdjacentIDs”. I need to find which "SiteIDs" are in each others database or Row in this case.

Example:

SiteID ADJ1 ADJ2 ADJ3 ADJ4 ADJ5 ADJ6
2002 2003 2004 2005 2057 2007 2008
2003 2002 2005 2004 2007 2057 2008
2004 2057 2005 2007 2002 2003 2008
2005 2007 2008 2004 2002 2003 2057

I want to Identify all Sites that are not in each other's adjacency list. Example: Site 2002 has Site 2003 in its list, But is Site 2003 in Sites 2002s list as well? In this case yes, but I want to run a query that queries the whole array.

 

[ToeShot]

You are going to need to write some code to do this. You will not be able to do it in a query. I have a small program now that will take two rows of dataand compare each item in row one and see if that item matches the corresponding item in row two. I ma sure with some manipulation it could do what you are asking for. I could send it to you or if you need help I can work on it. Let me know.
Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[demoman]

What you are looking at is a classical exploding bill of materials type of problem. In otherwards, you need to recursively create each possible unique relationship. As ToeShot stated, you will have to do some programming.

Your final result would look something like:

2002,2003
2002,2004
2002,2005
2002,2006
2003,2002
2003,2004
2003,2005
etc

So, ToeShot has some code to help you out. In addition, you should be able to 'search' for 'exploding BOM' problem (or something similar). This has been a part of MRP systems for a couple decades.