Really Daring SQL-Query: with Cows having fun!

Hi,
For my new application I need some really daring and special query, and i
don't know how to do this the best way. I hope somebody can help me.
The situation:
I have 2 tables:
tblMyCows: table with all my cows in it
--
CowID: unique ID of a Cow
CowName: name of my cow etc
tblCowHadIntercourse: table that links the Cows that had intercourse with
each other
--
Cow1ID: ID of the first Cow
Cow2ID: ID of the second Cow
-> so Cow1ID and Cow2ID had intercourse with each other :-)
Cows always have intercourse with another cow, and only one Cow at a time,
but 1 Cow can have intercourse with any other Cow.
now comes the Problem:
Suddenly one of my cows has a disease (no wonder of they have intercourse
like that!). And I need to know the CowID's of all the cows that may have
this disease also. So I need:
- all the CowID's of the Cows that had intercourse with the sick Cow = cow
group 1
- all the CowID's of the Cows that had intercourse with the Cows my sick cow
had intercourse
with
- all the CowID's of the Cows that had intercourse with the Cows that had
intercourse with
the Cows my sick cow had intercourse with
- ...
So basicly it should give me all the potential victims of the disease.
I hope I explained the problem good enough. And I really hope somebody can
help me with this, I'm really stuck on it! Note that each cow can be in
Cow1ID and in Cow2ID for another record...
Thanks a lot in advance,
PieterI think found it already myself, thanks to this solution:
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20034311.html[/ur
l]
Although I had to modify ot because I don't work with a parent-row or simple
tree-structure, but with an even relationship (as like every branch could be
of several tee's...). Can anybody confirm me that this works correct? It
seems so after some tests...
**************************
drop table #b
create table #b (ID integer, level int, lr int)
declare @.i int
select @.i = 1
insert #b select 1, 1, 3
while @.@.rowcount <> 0
begin
select @.i = @.i + 1
insert #b
select distinct Cow2ID, @.i, 0
from tblCowHadIntercourse, #b
where Cow1ID = #b.id
and Cow2ID not in (select id from #b where (ID = Cow2ID) and (lr = 0))
insert #b
select distinct Cow1ID, @.i, 1
from tblCowHadIntercourse, #b
where Cow2ID = #b.id
and Cow1ID not in (select id from #b where (ID = Cow1ID) and (lr = 1))
end
select distinct id
from #b
order by id
*******************************
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23yx%23RAKeFHA.2700@.tk2msftngp13.phx.gbl...
> Hi,
> For my new application I need some really daring and special query, and i
> don't know how to do this the best way. I hope somebody can help me.
> The situation:
> I have 2 tables:
> tblMyCows: table with all my cows in it
> --
> CowID: unique ID of a Cow
> CowName: name of my cow etc
> tblCowHadIntercourse: table that links the Cows that had intercourse with
> each other
> --
> Cow1ID: ID of the first Cow
> Cow2ID: ID of the second Cow
> -> so Cow1ID and Cow2ID had intercourse with each other :-)
> Cows always have intercourse with another cow, and only one Cow at a time,
> but 1 Cow can have intercourse with any other Cow.
> now comes the Problem:
> Suddenly one of my cows has a disease (no wonder of they have intercourse
> like that!). And I need to know the CowID's of all the cows that may have
> this disease also. So I need:
> - all the CowID's of the Cows that had intercourse with the sick Cow = cow
> group 1
> - all the CowID's of the Cows that had intercourse with the Cows my sick
cow
> had intercourse
> with
> - all the CowID's of the Cows that had intercourse with the Cows that had
> intercourse with
> the Cows my sick cow had intercourse with
> - ...
> So basicly it should give me all the potential victims of the disease.
> I hope I explained the problem good enough. And I really hope somebody can
> help me with this, I'm really stuck on it! Note that each cow can be in
> Cow1ID and in Cow2ID for another record...
> Thanks a lot in advance,
> Pieter
>
>|||I really don't know. After a short break I oticed that I did some things
wrong. My new solution:
*************************
create table #b (ID integer, level int)
declare @.i int
select @.i = 1
insert #b select 1, 1
while @.@.rowcount <> 0
begin
select @.i = @.i + 1
insert #b
select distinct Cow2ID, @.i
from tblCowLink, #b
where Cow1ID = #b.id
and Cow2ID not in (select id from #b)
union
select distinct Cow1ID, @.i
from tblCowLink, #b
where Cow2ID = #b.id
and Cow1ID not in (select id from #b)
end
select distinct id
from #b
order by id
*************************
I'm kind of sure it works good. although it is a little slower of the other
one, but I don't think the other one is really 100% correct (although I
can't find out what could be wrong with it and the results are always ok)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:eYJyCHLeFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I think found it already myself, thanks to this solution:
>
http://www.experts-exchange.com/Dat...Q_20034311.htmln">
> Although I had to modify ot because I don't work with a parent-row or
simple
> tree-structure, but with an even relationship (as like every branch could
be
> of several tee's...). Can anybody confirm me that this works correct? It
> seems so after some tests...
>
> **************************
> drop table #b
> create table #b (ID integer, level int, lr int)
> declare @.i int
> select @.i = 1
> insert #b select 1, 1, 3
> while @.@.rowcount <> 0
> begin
> select @.i = @.i + 1
> insert #b
> select distinct Cow2ID, @.i, 0
> from tblCowHadIntercourse, #b
> where Cow1ID = #b.id
> and Cow2ID not in (select id from #b where (ID = Cow2ID) and (lr = 0))
> insert #b
> select distinct Cow1ID, @.i, 1
> from tblCowHadIntercourse, #b
> where Cow2ID = #b.id
> and Cow1ID not in (select id from #b where (ID = Cow1ID) and (lr = 1))
> end
> select distinct id
> from #b
> order by id
> *******************************
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:%23yx%23RAKeFHA.2700@.tk2msftngp13.phx.gbl...
i[vbcol=seagreen]
with[vbcol=seagreen]
time,[vbcol=seagreen]
intercourse[vbcol=seagreen]
have[vbcol=seagreen]
cow[vbcol=seagreen]
> cow
had[vbcol=seagreen]
can[vbcol=seagreen]
>|||I was working on the sollution, some time passes over,
other solutions come in view, so it's a bit late.
But still here is a solution.
It is a leasy solution but stil complete.
It doesn't depend on existing tables, all tables
get created on the fly. (And cleaned up afterwards).
cowcow contains all the relations.
dcowcow containts all the relations in both directions. (d stands for
double)
cowlevel contains all the 'linked' cows and the number of removed links
I do not know what the purpose of the code is, so I left out the iteration
coding, you have to run the repeat part by 'hand'.
-- START OF DE CODE EXAMPLE
-- ADD MORE RELATIONS WITH THE INSERT COWCOW LINES
-- RUN THE PART BETWEEN THE XX LINES THE REQUIRED NUMBER OF TIMES
--
-- prepare source table with data
--
select 1 as cow_id1, 2 as cow_id2 into cowcow
insert cowcow values(1,3)
insert cowcow values(1,2)
insert cowcow values(2,3)
insert cowcow values(1,3)
insert cowcow values(2,8)
insert cowcow values(7,6)
insert cowcow values(2,16)
insert cowcow values(23,24)
insert cowcow values(56,7)
select * from cowcow
-- Make a second table
-- so the source that we can leave the source table as is.
select * into dcowcow from cowcow
-- Duplicate all entries in mirror
-- So that we do not have to concern ourselfs with the who is the first
and/or second cow
insert into dcowcow (cow_id1, cow_id2) select cow_id2, cow_id1 from cowcow
select * from dcowcow
-- Put the first sick cow into the sick table
-- called cowlevel (In this example the sick cow is 1)
select 1 cow_id1, 0 levelx into cowlevel
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXxx
-- Repeat the following code (by hand ?) until the table does not grow.
--
declare @.level int
select @.level = max(levelx)+1 from cowlevel
print @.level
insert into cowlevel (cow_id1, levelx)
select cow_id1, @.level
from dcowcow D where
exists (select * from cowlevel L where d.cow_id2 = L.cow_id1)
and not exists (select * from cowlevel L2 where d.cow_id1 = L2.cow_id1)
select * from cowlevel order by levelx
--
-- Repeat finishes here
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXxxx
-- Clean up
--
drop table cowcow
drop table dcowcow
drop table cowlevel
-- END OF THE CODE EXAMPLE
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:#yx#RAKeFHA.2700@.tk2msftngp13.phx.gbl...
> Hi,
> For my new application I need some really daring and special query, and i
> don't know how to do this the best way. I hope somebody can help me.
> The situation:
> I have 2 tables:
> tblMyCows: table with all my cows in it
> --
> CowID: unique ID of a Cow
> CowName: name of my cow etc
> tblCowHadIntercourse: table that links the Cows that had intercourse with
> each other
> --
> Cow1ID: ID of the first Cow
> Cow2ID: ID of the second Cow
> -> so Cow1ID and Cow2ID had intercourse with each other :-)
> Cows always have intercourse with another cow, and only one Cow at a time,
> but 1 Cow can have intercourse with any other Cow.
> now comes the Problem:
> Suddenly one of my cows has a disease (no wonder of they have intercourse
> like that!). And I need to know the CowID's of all the cows that may have
> this disease also. So I need:
> - all the CowID's of the Cows that had intercourse with the sick Cow = cow
> group 1
> - all the CowID's of the Cows that had intercourse with the Cows my sick
cow
> had intercourse
> with
> - all the CowID's of the Cows that had intercourse with the Cows that had
> intercourse with
> the Cows my sick cow had intercourse with
> - ...
> So basicly it should give me all the potential victims of the disease.
> I hope I explained the problem good enough. And I really hope somebody can
> help me with this, I'm really stuck on it! Note that each cow can be in
> Cow1ID and in Cow2ID for another record...
> Thanks a lot in advance,
> Pieter
>
>|||Thanks! It seems to have the same principles of one of the other solutions I
find. I guess this is the easiest and best working solution fr my problem.
Thanks a lot!
In fact, I'm not using it for cows, but for a document-system: I need to
have all the documents that have any link woth a certain document etc :-)
Pieter
"ben brugman" <ben@.niethier.nl> wrote in message
news:u7JodCNeFHA.1920@.tk2msftngp13.phx.gbl...
> I was working on the sollution, some time passes over,
> other solutions come in view, so it's a bit late.
> But still here is a solution.
> It is a leasy solution but stil complete.
> It doesn't depend on existing tables, all tables
> get created on the fly. (And cleaned up afterwards).
> cowcow contains all the relations.
> dcowcow containts all the relations in both directions. (d stands for
> double)
> cowlevel contains all the 'linked' cows and the number of removed links
> I do not know what the purpose of the code is, so I left out the iteration
> coding, you have to run the repeat part by 'hand'.
>
> -- START OF DE CODE EXAMPLE
> -- ADD MORE RELATIONS WITH THE INSERT COWCOW LINES
> -- RUN THE PART BETWEEN THE XX LINES THE REQUIRED NUMBER OF TIMES
> --
> -- prepare source table with data
> --
> select 1 as cow_id1, 2 as cow_id2 into cowcow
> insert cowcow values(1,3)
> insert cowcow values(1,2)
> insert cowcow values(2,3)
> insert cowcow values(1,3)
> insert cowcow values(2,8)
> insert cowcow values(7,6)
> insert cowcow values(2,16)
> insert cowcow values(23,24)
> insert cowcow values(56,7)
> select * from cowcow
>
> -- Make a second table
> -- so the source that we can leave the source table as is.
> select * into dcowcow from cowcow
> -- Duplicate all entries in mirror
> -- So that we do not have to concern ourselfs with the who is the first
> and/or second cow
> insert into dcowcow (cow_id1, cow_id2) select cow_id2, cow_id1 from cowcow
> select * from dcowcow
> -- Put the first sick cow into the sick table
> -- called cowlevel (In this example the sick cow is 1)
> select 1 cow_id1, 0 levelx into cowlevel
> -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXxx
> -- Repeat the following code (by hand ?) until the table does not grow.
> --
> declare @.level int
> select @.level = max(levelx)+1 from cowlevel
> print @.level
> insert into cowlevel (cow_id1, levelx)
> select cow_id1, @.level
> from dcowcow D where
> exists (select * from cowlevel L where d.cow_id2 = L.cow_id1)
> and not exists (select * from cowlevel L2 where d.cow_id1 = L2.cow_id1)
> select * from cowlevel order by levelx
> --
> -- Repeat finishes here
> -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXxxx
>
> --
> -- Clean up
> --
> drop table cowcow
> drop table dcowcow
> drop table cowlevel
> -- END OF THE CODE EXAMPLE
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:#yx#RAKeFHA.2700@.tk2msftngp13.phx.gbl...
i[vbcol=seagreen]
with[vbcol=seagreen]
time,[vbcol=seagreen]
intercourse[vbcol=seagreen]
have[vbcol=seagreen]
cow[vbcol=seagreen]
> cow
had[vbcol=seagreen]
can[vbcol=seagreen]
>