CODEWITHSUNDEEP
mysql
amir-f
amir-f

Reputation: 731

"select" dilemma

I have a question... Suppose that I have a table that holds names of cities. something like this:

id  name  
1   Los Angeles
2   Madrid

And I have a table for users:

uid username locationid  hometownid
1   John     1           2

in which John is a user whose current location is Los Angeles and his hometown is Madrid. Is there anyway that I can retrieve the user's current location name and hometown name in one row with a SELECT query? If it's impossible, do you have any suggestions for a change in the database structure to make it possible?

Upvotes: 1

Views: 70

Answers (1)

Tieson T.
Tieson T.

Reputation: 21191

Yes, and it's extremely trivial - just add a JOIN to each relevant field in the user table.

SELECT u.username, c1.name AS location, c2.name AS hometown
FROM users AS u
LEFT JOIN cites AS c1 ON u.locationid = c1.id
LEFT JOIN cites AS c2 ON u.hometownid = c2.id
WHERE u.id = 1 LIMIT 1;

Upvotes: 4

Related Questions