Re: SQL help needed...

"Raj" <andhra_abbai23_at_yahoo.com> wrote in news:1113491638.281228.13070_at_f14g2000cwb.googlegroups.com:

> 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
>
>

Research the "CONNECT BY" clause Received on Fri Apr 15 2005 - 09:51:02 CDT