Bus Root Search algorithm

<p>&nbsp;Hi,<br><br>This is my table structure.<br><br>CREATE TABLE [dbo].[City] (<br>&nbsp; [CityID] int IDENTITY(1, 1) NOT NULL,<br>&nbsp; [City] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br>&nbsp; [Line1] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br>&nbsp; [Line2] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br>&nbsp; [Line3] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br>&nbsp; [Line4] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,<br>&nbsp; [Sequence1] int NULL,<br>&nbsp; [Sequence2] int NULL,<br>&nbsp; [Sequence3] int NULL,<br>&nbsp; [Sequence4] int NULL,<br>&nbsp;&nbsp; PRIMARY KEY CLUSTERED ([CityID]),<br>&nbsp; UNIQUE ([City])<br>)<br>ON [PRIMARY]<br>GO</p><p>&nbsp;</p><p>This is the sample data<br><br>INSERT City<br>SELECT 1,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; RED, NULL, NULL, 1, NULL, NULL UNION ALL<br>SELECT 2,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; RED, NULL, NULL, 2, NULL, NULL UNION ALL<br>SELECT 3,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; RED, GREEN, NULL, 3, 1, NULL UNION ALL<br>SELECT 4,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, GREEN, NULL, NULL, 2, NULL UNION ALL<br>SELECT 5,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, GREEN, BLUE, NULL, 3, 1 UNION ALL<br>SELECT 6,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, GREEN, NULL, NULL, 4, NULL UNION ALL<br>SELECT 7,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, NULL, BLUE, NULL, NULL, 2 UNION ALL<br>SELECT 8,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, NULL, BLUE, NULL, NULL, 3 UNION ALL<br>SELECT 9,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, NULL, BLUE, NULL, NULL, 4 UNION ALL<br>SELECT 10,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, NULL, BLUE, NULL, NULL, 5 UNION ALL<br>SELECT 11,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, GREEN, NULL, NULL, 5, NULL UNION ALL<br>SELECT 12,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, GREEN, NULL, NULL, 6, NULL UNION ALL<br>SELECT 13,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, GREEN, NULL, NULL, 7, NULL UNION ALL<br>SELECT 14,&nbsp;&nbsp;&nbsp; Q,&nbsp;&nbsp;&nbsp; NULL, GREEN, NULL, NULL, 8, NULL<br><br><br>The map and data are looked like this.<br><br><img src="http://www.drivehq.com/file/DF.aspx/TableData.JPG?isGallery=&amp;share=&amp;shareID=0&amp;fileID=261175633&amp;sesID=jjwlqv55dfjd1guolgdqnz55&amp;size=pv&amp;ft=12/12/2008%203:15:16%20AM&amp;pay=&amp;n=531" mce_src="http://www.drivehq.com/file/DF.aspx/TableData.JPG?isGallery=&amp;share=&amp;shareID=0&amp;fileID=261175633&amp;sesID=jjwlqv55dfjd1guolgdqnz55&amp;size=pv&amp;ft=12/12/2008%203:15:16%20AM&amp;pay=&amp;n=531" width="597" height="542"><br></p><p>&nbsp;</p><p>Line are the roads pass through the city. </p><p>Sequence is the where the road starts and where it ends. <br></p><p><br>If a person wants to travel from city <span style="font-weight: bold;">f</span> to <span style="font-weight: bold;">Q</span>, we have to look for <span style="font-weight: bold;">roads</span>(Line noted in colors) available.<br><br>For us Road Green is available and wants to travel from <span style="font-weight: bold;">Sequence2 </span>from <span style="font-weight: bold;">8</span> to <span style="font-weight: bold;">1</span> (the city is <span style="font-weight: bold;">E</span>). But we didn't reach <span style="font-weight: bold;">Q</span>. We have to look for another road from <span style="font-weight: bold;">E </span>which is <span style="font-weight: bold;">RED </span>and travel from there and reach the city <span style="font-weight: bold;">Q</span>.<br><br>How to write the query.</p>