Re: SQL help needed...

Thank you very much every one for your input. Let me first apologize for the typo in the last line. Here is the correct sample data with meaningful field names. I will describe them in a second here: create table test(property number, loan number);

insert into test values (2,2);
insert into test values (2,3);
insert into test values (2,5);
insert into test values (5,5);
insert into test values (5,7);
insert into test values (8,7);
insert into test values (9,10);
insert into test values (11,10);

Every property can have many loans associated with it. Similarly, a single loan can be taken against bunch of properties. (Say, loan is for a big amount and single property value is not enough). Hopefully the sample data above now makes sense. So combination of property and loan makes the primary key for the table. There are bunch of other fields in the table which doesn't matter for what we are trying to do here. Also, this is just one table and we are not talking about this linking to another table or any thing like that. That will keep it simple. Now I am trying to see if property 2 is related to other properties either directly or indirectly. Prop 2 has loans 2, 3, 5. I will now check if these loans (2,3,5) has any other properties other than 2. Yes, we have property 5. I would call property 2 is related to property 5. Now extending the logic, here in this case property 5 has loans 5, 7. Now let's check if loans 5, 7 has any other properties (other than the ones we already identified, i.e. 2,5). Yes, we have property 8. I would say prop 8 is related to prop 5 directly, and prop 8 is related to prop 2 indirectly through 5. It doesn't matter whether they are related directly or indirectly. I am interested in all of them. It is like we have property pool and loan pool. We are finding if they are related. Here is the result set I am envisioning. If I am intersted in finding related properties for prop 2, probably a tabular result as follows will give me what I am looking for:

Given Property     Related Properties
2                              2
2                              5
2                              8

Similarly for prop 9,
Given Property     Related Properties
9                              9
9                              11

It doesn't matter whether we show each property related to itself or not. Whatever works for you. I hope I am clear this time. I can write a program to do this (with recordsets and looping etc) but there should be a cool way of doing this with just the SQL. I just can't figure it out. Your input will be appreciated.

-Raj Received on Thu Apr 14 2005 - 10:13:58 CDT