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.

GautamD31 · Accepted Answer · 2013-09-04T12:27:30.047

5

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.

edited Sep 04 '13 at 12:27

answered Sep 04 '13 at 11:20

GautamD31

28,552
10
64
85

1

Sorry @Adsy2010 I forgotted `INNER` – GautamD31 Sep 04 '13 at 11:26
I didnt see even that is missing.Thanks once again @Adsy2010. – GautamD31 Sep 04 '13 at 11:27
@Adsy2010 In mysql unqualified join is inner join. – takwlasnie Sep 04 '13 at 11:30
@takwlasnie `unqualified` means..?? – GautamD31 Sep 04 '13 at 11:33
Hi Thanks for your code but i am getting Unknown column 'area_2.area_name' in 'field list'. Can you please help me :) – Manan Sep 04 '13 at 11:36
1

@Gautam3164 if you write just JOIN it is the same as INNER JOIN. It's like INNER JOIN is the default join in mysql if it's not sppecified. So it's fine to write just JOIN. – takwlasnie Sep 04 '13 at 11:38
Learn something new every day – Adsy2010 Sep 04 '13 at 11:41
@Adsy2010 it is for me..?? – GautamD31 Sep 04 '13 at 11:42
Hi your query is runing but in home_location i am getting 40,50,24. I need this: Pioneer, Choa Chu Kang, Clementi – Manan Sep 04 '13 at 11:43
@Gautam3164 You query is perfect. Need your small help i want also home_location and job_location as number. Can you help me plz :) – Manan Sep 04 '13 at 11:49
Simply add `area_1.area_id as home_location_id, area_2.area_id as job_location_id` as like in my ans now – GautamD31 Sep 04 '13 at 11:51
1

@Gautam3164 Thank you for everything Gautam :) – Manan Sep 04 '13 at 11:52

score 1 · Answer 2 · answered Sep 04 '13 at 11:24

1

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

answered Sep 04 '13 at 11:24

Awais Amir

165
10

score 0 · Answer 3 · edited May 23 '17 at 10:28

0

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.

edited May 23 '17 at 10:28

Community

1
1

answered Sep 04 '13 at 11:21

Fluffeh

33,228
16
67
80

MySQL join two table matching with their id

3 Answers3