CODEWITHSUNDEEP
phpmysqlsql
Manan
Manan

Reputation: 1207

MySQL join two table matching with their id

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.

Upvotes: 3

Views: 1131

Answers (3)

GautamD31
GautamD31

Reputation: 28763

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.

Upvotes: 5

Awais Amir
Awais Amir

Reputation: 165

Select U.ID,U.Name,A1.Area_Name as Home_Location, A2.Area_name as Job_Location
From Users U
Left Outer Join Area A1 ON U.home_location = A1.Area_ID
Left Outer Join Area A2 ON U.job_location = A2.Area_ID

Upvotes: 1

Fluffeh
Fluffeh

Reputation: 33502

The SQL is this:

select
    us.name,
    us.home_location,
    ar.job_location
from
    user us
        join area ar
            on us.area_id=ar.id

but please, take a read of this Q&A I wrote for justthis situation.

Upvotes: 0

Related Questions