SQL gurus, please help

[Raj]

Hi,

I have a table in which two fields(FirstID, SecondID) together make the
primary key for the table. Let's look at the following example:

FirstID SecondID
******* ********
2 2
2 3
2 5
5 5
5 7
8 7
9 10
11 12

Here is what I am trying to do. For a given FirstID (say, 2) find all
SecondID with this FirstID (2,3,5). Now for these second ID's is there
a FirstID other than 2? (Yes, 5). I would say FirstID=2 is related to
FirstID = 5. Extending the logic FirstID = 5 is related to FirstID = 8.
I am trying to write a SQL (some kind of self join, I think) which will
give me all related FirstID for a given First ID. For First ID = 2 the
SQL will give me 5 and 8 in the above example. For First ID = 9 the SQL
will give me 11.
Any help will be greatly appreciated. Thanks!!

-Raj

[David Portas]

Re: SQL gurus, please help

Without knowing exactly what you are doing or seeing the complete
design, including keys, it's difficult to suggest whether a different
design might help here. Don't these "IDs" actually reference other
tables? Are we dealing with one or two entities here? Google this group
and microsoft.publi c.sqlserver.pro gramming for many other solutions and
examples of Trees and Hierarchies.

If the design is fixed then you'll have to do this iteratively, unless
you can set some upper bound on the number of levels, in which case it
should be possible using a fixed number of self-outer joins.

See also this post, which solves a similar problem:

http://www.google.co.uk/groups?selm=XISdne6OuuQE6qvdRVn-hA%40giganews.com

--
David Portas
SQL Server MVP
--

[Mark]

Re: SQL gurus, please help

create table TestIDs(FirstID int not null,SecondID int not null,
primary key(FirstID,Sec ondID))
insert into TestIDs(FirstID ,SecondID) values(2,2)
insert into TestIDs(FirstID ,SecondID) values(2,3)
insert into TestIDs(FirstID ,SecondID) values(2,5)
insert into TestIDs(FirstID ,SecondID) values(5,5)
insert into TestIDs(FirstID ,SecondID) values(5,7)
insert into TestIDs(FirstID ,SecondID) values(8,7)
insert into TestIDs(FirstID ,SecondID) values(9,10)
insert into TestIDs(FirstID ,SecondID) values(11,12)

select t1.FirstID as ID,t2.FirstID as RelatedID
from TestIDs t1
inner join TestIDs t2 on t2.SecondID=t1. SecondID and
t2.FirstID<>t1. FirstID
order by t1.FirstID,t2.F irstID

I think your sample data is wrong for First ID = 9, try adding

insert into TestIDs(FirstID ,SecondID) values(11,10)

[Raj]

Re: SQL gurus, please help

Thank you David and Mark for your input. I have looked into both of
your solutions but neither one of them does what I am looking for. I
will describe in detail like what I am looking for. Let me first
apologize for the typo in the last line in my original post. Here is
the correct sample data with meaningful field names. I will describe
them in a second here:
create table test(property int, loan int);
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. There is no
limit on how deep it can go. 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 greatly appreciated.


-Raj

[Ross Presser]

Re: SQL gurus, please help

> I can write a[color=blue]
>
> 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 greatly appreciated.
>
> -Raj[/color]

Well, there's no "cool way of doing it just with SQL" that I can think of,
except iterative. Here's a table-returning UDF that does what you want.
For large sets, you have *got* to have indexes on the test.property and
test.loan columns.

create function dbo.FindRelated
( @GivenProperty int )
returns @properties table (property int)
AS

BEGIN
declare @loans table (loan int)

insert into @properties values (@GivenProperty )

declare @n int
set @n = 1

while @n > 0
BEGIN
set @n = (select count(distinct dbo.test.loan) from dbo.test, @properties
P
where dbo.test.proper ty=P.property
and not exists (select * from @loans L where L.loan = dbo.test.loan))
if @N = 0
break
insert into @loans
select distinct loan from dbo.test, @properties P
where dbo.test.proper ty=P.property
and not exists (select * from @loans L where L.loan = dbo.test.loan)

set @n = (select count(distinct dbo.test.proper ty) from dbo.test, @loans
L
where dbo.test.loan = L.loan
and not exists (select * from @properties P where P.property =
dbo.test.proper ty))
if @N = 0
break
insert into @properties
select distinct property from dbo.test, @loans L
where dbo.test.loan = L.loan
and not exists (select * from @properties P where P.property =
dbo.test.proper ty)
END
return
END
go

Sample executions:

select * from test
select 2 GivenProperty, property RelatedProperty from dbo.FindRelated (2) F
select 5 GivenProperty, property RelatedProperty from dbo.FindRelated (5)
select 9 GivenProperty, property RelatedProperty from dbo.FindRelated (9)

property loan
----------- -----------
2 2
2 3
2 5
5 5
5 7
8 7
9 10
11 10

GivenProperty RelatedProperty
------------- ---------------
2 2
2 5
2 8

GivenProperty RelatedProperty
------------- ---------------
5 5
5 2
5 8

GivenProperty RelatedProperty
------------- ---------------
9 9
9 11

[Mark]

Re: SQL gurus, please help

select GivenProperty,R elatedProperty
from (
select f1.property as GivenProperty,b 1.property as RelatedProperty
from test f1
inner join test b1 on b1.loan=f1.loan

union

select f1.property,b2. property
from test f1
inner join test b1 on b1.loan=f1.loan
inner join test f2 on f2.property=b1. property
inner join test b2 on b2.loan=f2.loan

union

select f1.property,b3. property
from test f1
inner join test b1 on b1.loan=f1.loan
inner join test f2 on f2.property=b1. property
inner join test b2 on b2.loan=f2.loan
inner join test f3 on f3.property=b2. property
inner join test b3 on b3.loan=f3.loan

union

select f1.property,b4. property
from test f1
inner join test b1 on b1.loan=f1.loan
inner join test f2 on f2.property=b1. property
inner join test b2 on b2.loan=f2.loan
inner join test f3 on f3.property=b2. property
inner join test b3 on b3.loan=f3.loan
inner join test f4 on f4.property=b3. property
inner join test b4 on b4.loan=f4.loan

) as Deriv
--where GivenProperty=2
order by GivenProperty,R elatedProperty

I can't see a way of doing this in a single SQL statement if you don't
know the maximum level of nesting in advance

HTH

[Raj]

Re: SQL gurus, please help

Thank you Ross and Mark for your help. Ross's solution is preferable in
my case since I don't know the maximum level of testing. I did try it
out and it works.
Thanks again!!

-Raj