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.