CODEWITHSUNDEEP
sqloracle-database
Sagar Arora
Sagar Arora

Reputation: 13

Joining 2 Tables (Having 3 common columns) on the basis of only two common columns

I have 2 Tables - Employee and Job_History and Both the tables have 3 common columns in it - EMPLOYEE_ID, JOB_ID, DEPARTMENT_ID.

Now, I want to Join both the tables using JOIN but not on the basis of all 3 common columns BUT on the basis of only 2 common columns - EMPLOYEE_ID and JOB_ID.

How can i get the result ?

Upvotes: 1

Views: 2460

Answers (2)

JYoThI
JYoThI

Reputation: 12085

You can pass multiple condition in JOIN on clause like this 

select E.* ,J.* 
from Employee as E 
   inner join Job_History as J 
   on (E.EMPLOYEE_ID=J.EMPLOYEE_ID 
        and E.JOB_ID=J.JOB_ID)

Upvotes: 1

McGlothlin
McGlothlin

Reputation: 2099

You can JOIN on as many or as few columns as you'd like. The JOIN conditions tells the database how to merge the data from the tables.

SELECT EMPLOYEE_ID 
FROM EMPLOYEES
JOIN JOB_HISTORY USING (EMPLOYEE_ID, JOB_ID)

If the values in DEPARTMENT_ID also match up in both tables where EMPLOYEE_ID and JOB_ID are equivalent, the result set will be the same regardless of whether or not you also include DEPARTMENT_ID.

If I have the following columns in each table:

Table A
EMPLOYEE_ID  DEPARTMENT_ID  JOB_ID  Other_Column1  Other_Column2
1            1              1       stuff          things
2            2              2       stuff          things
3            3              3       stuff          things

Table B
EMPLOYEE_ID  DEPARTMENT_ID  JOB_ID  Other_Column3  Other_Column4
1            1              1       stuff          things
2            2              2       stuff          things
3            3              3       stuff          things

then the joins would be equivalent whether you include DEPARTMENT_ID or not. However if DEPARTMENT_ID were 1, 2, 3 in table A and 2, 3, 4 in table B:

Table A
EMPLOYEE_ID  DEPARTMENT_ID  JOB_ID  Other_Column1  Other_Column2
1            1              1       stuff          things
2            2              2       stuff          things
3            3              3       stuff          things

Table B
EMPLOYEE_ID  DEPARTMENT_ID  JOB_ID  Other_Column3  Other_Column4
1            2              1       stuff          things
2            3              2       stuff          things
3            4              3       stuff          things

the result set would return no results because there are no cases where the three columns, EMPLOYEE_ID, DEPARTMENT_ID, and JOB_ID all match for a given row.

Upvotes: 1

Related Questions