MySQL join two table matching with their id

Question

I have this two table:

Table One: user

id   name   home_location    job_location
1    Jack   40               5
2    Rocky  50               4  
3    Tom    24               9

Table Two: area

area_id    area_name
5          Bukit Batok
4          Bukit Panjang
9          Boon Lay
40         Pioneer
50         Choa Chu Kang
24         Clementi

I want result like this:

ID    name    home_location   job_location
1     Jack    Pioneer         Bukit Batok
2     Rocky   Choa Chu Kang   Bukit Panjang
3     Tom     Clementi        Boon Lay

As i am not good in sql query so how to write select query. Any ideas or suggestions? Thanks.

Answer 1

Try like

SELECT id as ID,
             name,
             area_1.area_name as home_location,
             area_2.area_name as job_location,
             area_1.area_id as home_location_id,
             area_2.area_id as job_location_id   
FROM user 
INNER JOIN 
      area AS area_1 
           ON area_1.area_id = user.home_location
INNER JOIN 
      area AS area_2
           ON area_2.area_id = user.job_location

And try to avoid mysql_* statements due to the entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_*, is officially deprecated as of PHP v5.5.0 and will be removed in the future.

There are two other MySQL extensions that you can better Use: MySQLi and PDO_MySQL, either of which can be used instead of ext/mysql.