Join 2 tables on 2 different conditions and accessin results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Join 2 tables on 2 different conditions and accessin results

Post 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 :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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`
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Post 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 :)
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Well, what are the chances that your other table has a location_id?

If so, then specify which it's from.
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Post 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
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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.
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Post 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
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

Great, thanks for following up.
Post Reply