Complex Joins

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:

Code:

---

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:

Code:

---

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

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.

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

Code:

---

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?

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