Page 1 of 1

Join 2 tables on 2 different conditions and accessin results

Posted: Fri Jun 01, 2007 11:11 am
by Dave2000
I have two tables.

One is called 'map' with fields 'id', 'x', and 'y'.
The other table is called 'users' with fields 'user_id, 'location_id' and 'destination_id'.

For starters I(we) will assume each table has only one row.

For each of location_id and destination_id, I wish to get the map coordinates.

I thought something like this might work, but I dont know how to access the results / how to distinguish between the location coordinates and destination coordinates. :?

Code: Select all

SELECT * FROM users
INNER JOIN map ON users.location_id = map.id 
INNER JOIN map ON users.destination_id = map.id
Any ideas how I should do this? - I guess I am taking the wrong approach... :?

Thanks

Shears :)

Posted: Fri Jun 01, 2007 11:15 am
by feyd
It's correct. You just need to alias the columns to make them distinguishable.

For example

Code: Select all

select location.x as location_x, location.y as location_y from map as location where location_id = map.id

Posted: Fri Jun 01, 2007 5:31 pm
by Dave2000
Hi feyd. I'm not really sure what you've written :( Why the alias on 'map'?

What you wrote doesn't work...

Code: Select all

WHERE location_id = map.id
Did you mean to put users.location_id ?

How can you reference two tables without a join? :?

Thanks

Posted: Fri Jun 01, 2007 7:13 pm
by superdezign
It's not a solution, it's an example. You're supposed to use that to figure out your problem.

And you can reference two tables without a join like so:

Code: Select all

SELECT `table1.id`, `table1.x`, `table2.location_id` FROM `table1`, `table2`

Posted: Fri Jun 01, 2007 9:54 pm
by Dave2000
superdezign wrote:It's not a solution, it's an example. You're supposed to use that to figure out your problem.
Where did I say what feyd wrote was meant to be a solution? :P What I meant was, I do not understand the example as valid SQL. :( "What you wrote doesn't work... " I wasn't trying to get it to work as a solution to the overall problem, but just as a piece of SQL...

Thanks anyway,

Shears :)

Posted: Fri Jun 01, 2007 10:30 pm
by bdlang
superdezign wrote: And you can reference two tables without a join like so:

Code: Select all

SELECT `table1.id`, `table1.x`, `table2.location_id` FROM `table1`, `table2`
That's still a join, it's just an implicit INNER JOIN using the comma to separate the tables.

I should mention that this statement returns a cartesian product, NOT a resultset based on matching keys across tables.

MySQL Manual : JOIN syntax

Posted: Sat Jun 02, 2007 5:13 am
by superdezign
bdlang wrote:
superdezign wrote: And you can reference two tables without a join like so:

Code: Select all

SELECT `table1.id`, `table1.x`, `table2.location_id` FROM `table1`, `table2`
That's still a join, it's just an implicit INNER JOIN using the comma to separate the tables.

I should mention that this statement returns a cartesian product, NOT a resultset based on matching keys across tables.

MySQL Manual : JOIN syntax
Noted and learned. :D

Posted: Sat Jun 02, 2007 8:35 pm
by Dave2000
So... is the example valid SQL?

While the example wont provide the whole solution, surely if it is valid SQL is should provide half the data... or not as the case seems to be :?

Code: Select all

WHERE location_id = map.id
If you need to reference 'id' with a preceding 'map.' , why doesn't 'location_id' need referencing with a preceding 'users.' ?

Thank you

Shears

Posted: Sat Jun 02, 2007 8:56 pm
by superdezign
Well, what are the chances that your other table has a location_id?

If so, then specify which it's from.

Posted: Sat Jun 02, 2007 10:06 pm
by Dave2000
From my very first post...
Shears wrote:I have two tables.

One is called 'map' with fields 'id', 'x', and 'y'.
The other table is called 'users' with fields 'user_id, 'location_id' and 'destination_id'.
Shears

Posted: Sat Jun 02, 2007 11:54 pm
by bdlang
Ok, the example given wasn't usable. At this point let's get past this and find out how to solve your problem.

Your original post has you performing a SELECT ALL on the `users` table, attempting a JOIN with the `map` table. Now, given that you claim to want the coordinates, this isn't going to work. Your `users` table only has details on the user, and his/her location and destination id values that map to the `map` table (no pun intended) where the coordinates are really stored. You really need to select some elements from each table, e.g.

(not a valid query)

Code: Select all

SELECT user, location X coord, location Y coord, destination X coord, destination Y coord
I find it a useful tool, especially when you're learning SQL, to write queries in plain english and then convert to valid SQL using the columns you have to work with.

As feyd alluded to, when you have tables with potentially ambiguous column names, you want to use an ALIAS on those and make sure each one is returned with a name you can access, similar to the 'plain english' example.

In this case, you also need to use an ALIAS on the `map` table, since you need to perform two joins.

This may be a more appropriate query (keeping in mind it is an example):

Code: Select all

SELECT
 users.userName
, map1.x AS location_x
, map1.y AS location_y
, map2.x AS destination_x
, map2.y AS destination_y
FROM users
INNER JOIN map AS map1 ON (map1.id = users.location_id)
INNER JOIN map AS map2 ON (map2.id = users.destination_id)
(As I find it hard to believe your `users` table only contains the three columns you've given, I've taken liberty to retrieve a `userName` column.)

Remember that the ALIAS name used is the name you'll reference that data in your code, assuming you're using PHP or Perl or some other programming language to do something with the resultset.,
Please post back with any questions, let me know how that works for you.

Posted: Fri Jun 22, 2007 10:35 am
by Dave2000
Hi,

Sorry for the long delay in my response. :oops: Thank you for posting bdlang. I had not previously come across aliasing tables in that manner. I cant think of any questions because you explained everything very well.

Shears

Posted: Fri Jun 22, 2007 11:34 am
by bdlang
Great, thanks for following up.