Results 1 to 4 of 4

Thread: Complex Joins

  1. #1
    Join Date
    Jun 2010
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default 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

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,239
    Thanks
    96
    Thanked 103 Times in 101 Posts

    Default

    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.
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #3
    Join Date
    Jun 2010
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default

    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?

  4. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,239
    Thanks
    96
    Thanked 103 Times in 101 Posts

    Default

    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
    To choose the lesser of two evils is still to choose evil. My personal site

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •