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,

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" <speralta@progressivetrail.org> wrote in message
news:1172709056.569093.50770@s48g2000cws.googlegro ups.com...
>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?
>

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" <altereg...@dslextreme.com> wrote:
> 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" <spera...@progressivetrail.org> wrote in message
>
> news:1172709056.569093.50770@s48g2000cws.googlegro ups.com...
>
> >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?

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" <spera...@progressivetrail.org> wrote:
> 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" <altereg...@dslextreme.com> wrote:
>
> > 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" <spera...@progressivetrail.org> wrote in message
>
> >news:1172709056.569093.50770@s48g2000cws.googlegr oups.com...
>
> > >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?

On 1 Mar, 03:19, "salvador" <spera...@progressivetrail.org> wrote:
> 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" <spera...@progressivetrail.org> wrote:
>
>
>
> > 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" <altereg...@dslextreme.com> wrote:
>
> > > 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" <spera...@progressivetrail.org> wrote in message
>
> > >news:1172709056.569093.50770@s48g2000cws.googlegr oups.com...
>
> > > >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?- Hide quoted text -
>
> - Show quoted text -

Just list the JOINs in order.

On Mar 1, 1:37 am, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
> On 1 Mar, 03:19, "salvador" <spera...@progressivetrail.org> wrote:
>
>
>
> > 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" <spera...@progressivetrail.org> wrote:
>
> > > 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" <altereg...@dslextreme.com> wrote:
>
> > > > 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" <spera...@progressivetrail.org> wrote in message
>
> > > >news:1172709056.569093.50770@s48g2000cws.googlegr oups.com...
>
> > > > >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?- Hide quoted text -
>
> > - Show quoted text -
>
> Just list the JOINs in order.

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

On 1 Mar, 21:45, "salvador" <spera...@progressivetrail.org> wrote:
> Hmm... couldn't get that to work. I just broke it into 2 seperate
> queries.

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.