Re: SQL help needed...

IANAL_VISTA wrote:
> "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

Here is an attempt that should work in 10g:

select gp given_property, rp related_property from( select distinct connect_by_root gp gp, rp from( select distinct a.property gp, b.property rp from test a, test b
where a.loan = b.loan
) connect by nocycle prior rp = gp
) where gp = 2;

Hope that helps.

Justis Received on Fri Apr 15 2005 - 13:49:22 CDT