Complex Joins

[timbim]

Hi all, I've got a problem regarding joins, specifically joining a one table to another twice in the same query. This is MySQL if it matters. Tables something like this:

TABLE:Locations
.....ID...Location
.....1....Home
.....2....Gym
.....3....Work
.....4....Post Office

TABLE:ROUTES
.....ID....StartID....EndID.....Distance....Via
.....1........1.........2..........4.5......Elmo's
.....2........1.........3..........1.2......Direct
.....3........3.........4..........0.5......Clockwise

I want a query that will give me the name of the start and end location, and the distance, for each route. I've tried:

SELECT Locations.Location AS Start, Locations.Location AS End, Routes.Distance AS Distance FROM Locations JOIN Routes ON Locations.ID=Routes.StartID JOIN Locations ON Routes.EndID=Locations.ID

MySQL doesn't like that, understandably, but I can't see what to do. I'm assuming that it can be done.

Possibly important notes:

• There will never be a route from a location to the same location
• Not all possible routes will be completed
• Some routes will have the reverse completed with a different distance (one way streets etc)
• Some routes will be present twice, with different values for Via

Many thanks,
Tim

[james438]

unless someone answers this first I'll try to have an answer for you later tonight. This is not my area of knowledge, but I have dabbled with this in the past and recently as well, but I won't be opposed to someone posting a solution before I do.

[timbim]

I've actually just found a solution by trawling through some somewhat complex google searches, the query

SELECT R.ID, S.Location, E.Location FROM Routes AS R, Locations AS S, Locations AS E WHERE S.ID = R.StartID AND E.ID = R.EndID

solves the problem. It really turns on the fact that you give one table two aliases.

I don't fully understand the query however. I take it to mean that where in this query is analogous to join, but could be somewhat ambiguous. Am I right?

[james438]

I am glad you found a solution. In my short and limited experiences with Join I have noticed that there are several ways to write the same query. for example:

SELECT * FROM test1 JOIN test2 WHERE test1.col1=test2.col3
is the same as
SELECT * FROM test1,test2 WHERE test1.col1=test2.col3
is the same as
SELECT * FROM test1 JOIN test2 ON test2.col3=test1.col1
is the same as
SELECT * FROM test1 JOIN test2 ON test1.col1=test2.col3