Complex SQL

[rodjor]

I am trying to setup a query to search across a table multiple times.

I have several buildings connected by fiber and not all of them connect to each other obviously but I need to find a connection between two buildings and there maybe one building between them or 5 buildings between them but I am having a hard time creating a query to do this.

Any help would be greatly appreciated.

Thanks
Rick

 

[r937]

a lot will depend on how the buildings are related

what do your tables look like?

and the relationships, are they bidirectional? in other words, if A is connected to B, how is that represented, and is B connected to A also represented?

can't help you much more on what you've given so far

rudy

http://r937.com/

SQL Consulting

 

[rodjor]

Bldg Bldg_to Mode
11 12 MM
12 13 MM
13 14 MM
14 15 MM

16 17 MM
21 22 MM

Above is an example of the table. If I want to get to building 11 to 15 you will have to get from 11-12-13-14-15

How do I program this to find the buildings in between two buildings and stopping if there is only building between them or if they are connected to each other?

 

[r937]

so the connections are in one direction only? or you always record the lower number?

for example, you could just as easily record

12 11 MM
13 12 MM
14 13 MM
15 14 MM


rudy

http://r937.com/

SQL Consulting

 

[rodjor]

No there maybe more buildings involved that was just an example from the table. There are approximately 424 different connections between different buildings, but there is always a way to connect the buildings through other buildings. I am just trying to figure out a way to write an SQL statement to find the connecting buildings.


I ask the user for the two buildings he would like the connection to. He inputs Bldg 1 to Bldg 8 and I would output this:

bldg 1 connects to bldg 3 through bldg 5 through bldg 6 ending in bldg 8

I am just unsure how to cycle through the table to find the connections.

 

[r937]

there are how many buildings?

i'm thinking of an 8-table self-join, but the more i think of it, the more i don't know if simple joins are going to solve this...




rudy

http://r937.com/

SQL Consulting

 

[rodjor]

424 different connections some containing the same building but different connecting buildings.

 

[r937]

how many buildings?

rudy

http://r937.com/

SQL Consulting

 

[rodjor]

142 buildings

 

[r937]

okay, then the self-join approach won't work


you may have to play for a while with this:

http://www.dbmsmag.com/9704d06.html

rudy

http://r937.com/

SQL Consulting

 

[SQLSister]

YOu could do a search for recursion on google. I'm sure there are lots of articles about it.