Professional Web Applications Themes

Multiple joins involving 3 tables - How to? - MySQL

I have 3 tables in a mysql database. The first table contains a list of cities with latitude and longitude coordinates. city, lat, lon The second table contains a list of locations in these cities with latitude and longitude coordinates. unique id, lat, lon The third table contains a list of locations in these cities with information about the location. unique id, city, field 1, field 2, etc. I am passing in a latitude and longitude value that I would like to use to grab the city name (e.g., "Bend") from the first table that I will then use as ...

  1. #1

    Default Multiple joins involving 3 tables - How to?

    I have 3 tables in a mysql database.

    The first table contains a list of cities with latitude and longitude
    coordinates.
    city, lat, lon
    The second table contains a list of locations in these cities with
    latitude and longitude coordinates.
    unique id, lat, lon
    The third table contains a list of locations in these cities with
    information about the location.
    unique id, city, field 1, field 2, etc.

    I am passing in a latitude and longitude value that I would like to
    use to grab the city name (e.g., "Bend") from the first table that I
    will then use as a key to grab values from the other two tables using
    another join, like so:

    select coordinates.lat,coordinates.lon,location.val1, location.val2
    from coordinates left join location ON location.ml = coordinates.ml
    WHERE location.city="Bend";

    The problem is, I'm not sure how to use a join to get the City name.

    Can anyone point me in the right direction? I've never done multiple
    joins in this way. Is it even possible w/mysql?

    salvador Guest

  2. #2

    Default Re: Multiple joins involving 3 tables - How to?

    salvador,

    Unless the uniqueid for table2 is the same unique id as table3 you can't get
    what you want. The only thing you can do is find the city that is closest,
    and even that is not always correct. Here is why:

    CityA at has a "radius of 5 miles".
    CityB, the city adjacent to it has a "radius" of 1 mile.
    Published Lat/Lon for cities is usually at their "geocenter".

    If I reside in CityA 4.5 miles from the geocenter of CityA on the CityB side
    of town, my Lat/Lon is actually closer to the geocenter CityB which is 1.5
    miles. Couple that with the fact that cities are not "round" and you can see
    the problem.

    Maybe some of my assumptions based upon the description of the problem are
    incorrect. Post all of your DDL along with some data samples (in the form of
    insert statments) and maybe we can get you there.

    BTW, you can search Google for "calculate shortest distance between two
    points" to get a couple of the algorithms.

    -- Bill

    "salvador" <org> wrote in message
    news:googlegroups.com... 


    AlterEgo Guest

  3. #3

    Default Re: Multiple joins involving 3 tables - How to?

    The unique Id is the same between table 2 and table 3. The real issue
    here is whether it's possible to get the output from a statement like
    the following:

    select cities.city from cities where lat="44.058333" AND
    lon="-121.314167";

    Joined to a statement like this one:

    select coordinates.lat,coordinates.lon,location.val1, location.val2
    from coordinates left join location ON location.ml = coordinates.ml
    WHERE location.city="Bend";

    On Feb 28, 5:36 pm, "AlterEgo" <com> wrote: 




    > [/ref]


    salvador Guest

  4. #4

    Default Re: Multiple joins involving 3 tables - How to?

    Here's the relevent record layout:

    describe cities;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+----------------+
    | id | int(11) | | PRI | NULL | auto_increment |
    | city | varchar(30) | YES | | NULL | |
    | lat | double | YES | | NULL | |
    | lon | double | YES | | NULL | |
    +-------+-------------+------+-----+---------+----------------+

    mysql> describe location_coordinates;
    +-------+---------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+----------------+
    | ml | int(11) | YES | | NULL | |
    | lat | double | YES | | NULL | |
    | lon | double | YES | | NULL | |
    +-------+---------+------+-----+---------+----------------+

    mysql> describe location;
    +--------------------------+--------------+------+-----+---------
    +----------------+
    | Field | Type | Null | Key | Default |
    Extra |
    +--------------------------+--------------+------+-----+---------
    +----------------+
    | ml | int(11) | YES | | NULL |
    | city | varchar(30)|

    .... etc...


    On Feb 28, 6:45 pm, "salvador" <org> wrote: 








    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref][/ref]


    salvador Guest

  5. #5

    Default Re: Multiple joins involving 3 tables - How to?

    On 1 Mar, 03:19, "salvador" <org> wrote: 




    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    >
    > - Show quoted text -[/ref]

    Just list the JOINs in order.

    Captain Guest

  6. #6

    Default Re: Multiple joins involving 3 tables - How to?

    On Mar 1, 1:37 am, "Captain Paralytic" <com> wrote: 





    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Just list the JOINs in order.[/ref]

    Hmm... couldn't get that to work. I just broke it into 2 seperate
    queries.

    salvador Guest

  7. #7

    Default Re: Multiple joins involving 3 tables - How to?

    On 1 Mar, 21:45, "salvador" <org> wrote: 

    When you say "couldn't get that to work", precisely what was it that
    didn't work about it?
    Post what you ended up with and I'll try and combine them for you.

    Captain Guest

Similar Threads

  1. Replies: 8
    Last Post: February 28th, 03:45 AM
  2. SQL, Three Tables, Joins and Groups...
    By Thomas in forum Coldfusion - Getting Started
    Replies: 10
    Last Post: February 4th, 06:40 PM
  3. multiple inner joins
    By Anderson11983 in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 4th, 08:06 AM
  4. Help on Multiple JOINS
    By Bob Barrows in forum ASP Database
    Replies: 0
    Last Post: July 9th, 09:36 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139