Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Query recursive selecting table relations

Author  Topic 

luspo
Starting Member

1 Post
Posted - 2004-02-07 : 14:41:59
I,
i want to describe data table relations on a table in whic i saved only first "step" of data relation.
the problem is similar to this:

i have a table that contains Origin and Destination of an airplane fly thought airports.

Example:

| planeId | StartFrom | Destination |
--------------------------------------
| HKK-QYZ | London | Paris |
| HKK-QYZ | Paris | N.York |
| 123-QYZ | Paris | Rome |
| 999-QYZ | Rome | Athen |
| 777-QYZ | Athen | Boston |
| 666-QYZ | Athen | Manchester |

....

i want to write a select (otherwise using a view can be optimal)
that retrieve all the step to go from London to Manchester.

In that case (i add a solution id):

| solutionId | planeId | StartFrom | Destination |
--------------------------------------------------
| 1 | HKK-QYZ | London | Paris |
| 1 | 123-QYZ | Paris | Rome |
| 1 | 999-QYZ | Rome | Athen |
| 1 | 666-QYZ | Athen | Manchester |

this explain what i want to do. Describe relations between
table one by one on a table and so select the travel of joins
from one table source to one other table.
Recursive query is not allowed in SQL Server. Other recursive
technique seems not match this goal.
How? any idea?
tnx.

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2004-02-07 : 16:23:20
See Books Online:

Accessing and Changing Data >> Advanced Query Concepts >>

>> Solutions to Common Query Puzzles >> Expanding Networks
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2004-02-09 : 06:51:29
.... or to use several "union" operators:

create table flights (cost int, p1 char(1), p2 char(1))
insert into flights
select 200, 'a', 'b' union all
select 200, 'b', 'a' union all
select 300, 'b', 'c' union all
select 300, 'c', 'b' union all
select 500, 'c', 'd' union all
select 500, 'd', 'c' union all
select 777, 'a', 'd' union all
select 777, 'd', 'a' union all
select 140, 'a', 'r' union all
select 140, 'r', 'a' union all
select 220, 'r', 'd' union all
select 220, 'd', 'r' union all
select 800, 'x', 'y' union all
select 800, 'y', 'x'

declare @p1 char(1), @p2 char(1)
set @p1='a' set @p2='d'

select cost, p1, p2, null, null, null, null
from flights where p1=@p1 and p2=@p2
union all
select f1.cost+f2.cost cost, f1.p1, f1.p2, f2.p1, f2.p2, null, null
from flights f1 inner join flights f2 on f1.p2=f2.p1
where f1.p1=@p1 and f2.p2=@p2
union all
select f1.cost+f2.cost+f3.cost, f1.p1, f1.p2, f2.p1, f2.p2, f3.p1, f3.p2
from flights f1 inner join flights f2 on f1.p2=f2.p1 inner join flights f3 on f2.p2=f3.p1
where f1.p1=@p1 and f3.p2=@p2 and f1.p2<>@p2 and f2.p2<>@p1

drop table flights

cost        p1   p2                       
----------- ---- ---- ---- ---- ---- ---- 
777         a    d    NULL NULL NULL NULL
360         a    r    r    d    NULL NULL
1000        a    b    b    c    c    d
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site