Bus Root Search algorithm - 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 2005 Forums

Transact-SQL (2005)

Bus Root Search algorithm

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2008-12-12 : 06:32:55

Hi,

This is my table structure.

CREATE TABLE [dbo].[City] (
[CityID] int IDENTITY(1, 1) NOT NULL,
[City] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Line1] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Line2] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Line3] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Line4] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Sequence1] int NULL,
[Sequence2] int NULL,
[Sequence3] int NULL,
[Sequence4] int NULL,
PRIMARY KEY CLUSTERED ([CityID]),
UNIQUE ([City])
)
ON [PRIMARY]
GO

My data is stored like this

I don't know why image is not visible.

I have also posted here and it is visible

http://sql-server-performance.com/Community/forums/t/29008.aspx

Above is the map.

Line are the roads pass through the city.

Sequence is the where the road starts and where it ends.

If a person wants to travel from city f to Q, we have to look for roads(Line noted in colors) available.

For us Road Green is available and wants to travel from Sequence2 from 8 to 1 (the city is E). But we didn't reach Q. We have to look for another road from E which is RED and travel from there and reach the city Q.

How to write the query.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-12 : 06:58:42

Is this homework? Should this not be in a few tables?

I started this but "from Sequence2 from 8 to 1 (the city is E)" confused me.

Select * from
(
select line1,line2,line3,line4 from city
where cityid >='f' and <='Q') as RoadsAvailable
where (line1='Green' or line2='Green' or line3='Green' or line4='Green') and .......

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-12 : 06:59:43

correction:
Select * from
(
select line1,line2,line3,line4 from city
where cityid >='f' and cityid<='Q') as RoadsAvailable
where (line1='Green' or line2='Green' or line3='Green' or line4='Green') and .......

baburk
Posting Yak Master

108 Posts

Posted - 2008-12-16 : 00:49:18

This is the sample data

INSERT City
SELECT Q, RED, NULL, NULL, 1, NULL, NULL UNION ALL
SELECT A, RED, NULL, NULL, 2, NULL, NULL UNION ALL
SELECT C, RED, GREEN, NULL, 3, 1, NULL UNION ALL
SELECT D, NULL, GREEN, NULL, NULL, 2, NULL UNION ALL
SELECT E, NULL, GREEN, BLUE, NULL, 3, 1 UNION ALL
SELECT F, NULL, GREEN, NULL, NULL, 4, NULL UNION ALL
SELECT G, NULL, NULL, BLUE, NULL, NULL, 2 UNION ALL
SELECT H, NULL, NULL, BLUE, NULL, NULL, 3 UNION ALL
SELECT J, NULL, NULL, BLUE, NULL, NULL, 4 UNION ALL
SELECT K, NULL, NULL, BLUE, NULL, NULL, 5 UNION ALL
SELECT L, NULL, GREEN, NULL, NULL, 5, NULL UNION ALL
SELECT O, NULL, GREEN, NULL, NULL, 6, NULL UNION ALL
SELECT P, NULL, GREEN, NULL, NULL, 7, NULL UNION ALL
SELECT X, NULL, GREEN, NULL, NULL, 8, NULL

The map and data are looked like this.

[url]http://www.drivehq.com/file/DF.aspx/TableData.JPG?isGallery=&share=&shareID=0&fileID=261175633&sesID=jjwlqv55dfjd1guolgdqnz55&size=pv&ft=12/12/2008%203:15:16%20AM&pay=&n=531[/url]

Thanks for the reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources