Complex Query question ?! - SQL Server Forums

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

SQL Server Development (2000)

Complex Query question ?!

Author

Topic

netcop
Starting Member

12 Posts

Posted - 2002-07-24 : 11:06:07

Hello everyone!

I have a table with buslines stored in it. For each line, two directions are defined.
The information provided are the start busstop and the end busstop.

For example i want from 'Victory Place' to 'Lakeside Software Park' then i have to check
that the distance from the 'start busstop' is < then the 'end busstop'. So you get the right direction (in this case 1).

When I want from 'Victory Place' to 'Mountain View' then I see that there is no direct connection to there, because the distance from 'V.P.' is higher. So I have to drive to the end station of the other direction - 'Lakeside Software Park' on direction 1.
Then I can choose 'LSP' to 'Mountain View'

I need a select statement which returns me the information, when I check 'Mountain View' as end-busstop, that there is only a indirect connection.

And i need a statement that returns me the info that when i want from 'Mozart street' to 'V.P.' that i've to choose first to drive to LSP.

Or both in one.
Can any one send me a query for this?!

create table lines (
line int,
direction int,
busstop nvarchar(32),
distance int )

insert lines values (90,1,'Victory Place',0)
insert lines values (90,1,'Event Hall',861)
insert lines values (90,1,'Messe',1061)
insert lines values (90,1,'Rosentaler Straße',1471)
insert lines values (90,1,'Hauptm.-Hermann-Platz',2886)
insert lines values (90,1,'Wine yard',3509)
insert lines values (90,1,'Mozart street',3778)
insert lines values (90,1,'Lakeside Software Park',4339)
insert lines values (90,2,'Lakeside Software Park',0)
insert lines values (90,2,'Mountain View',286)
insert lines values (90,2,'Sumpfweg',547)
insert lines values (90,2,'Stadion',1111)
insert lines values (90,2,'Wine yard',1376)
insert lines values (90,2,'Neugasse',1732)
insert lines values (90,2,'Hauptm.-Hermann-Platz',2022)
insert lines values (90,2,'Rosentaler Straße',3305)
insert lines values (90,2,'Messe',3884)
insert lines values (90,2,'Event Hall',4141)
insert lines values (90,2,'Victory Place',5086)

Any guidance would be gladly welcomed
Thanks

Michael

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-24 : 12:21:57

This should tell you that thre is no direct route
if not exists
(select * from lines l1, lines l2
where l1.busstop = @startStn
and l2.busstop = @endStn
and l1.line = l2.line
and l1.direction = l2.direction
and l1.distance < l2.distance
)

I'll give you the code for finding the station to change at if I get time.
Note that will be quite easy in this case but what will you do if you have a lot of diferent routes and maybe have to select a best one?

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources