CODEWITHSUNDEEP
sqloracle-databasejoin
stephenjacob
stephenjacob

Reputation: 171

how to use join for three tables

I have 3 tables. I want to fetch the employee details for those employees only in table3. But when i ran the query i am getting all the employees names from table1

Table1 and table 2 has many employees.

Table3 has only one employee name.

Is the below query is correct? need some advise !!

i am using left join because i want mis-match records also from table1

select emp.emp_name, emp.dept, sal.salary 
from table1 as emp 
   left join table2 as sal on emp.emp_name = sal.emp_name 
                          and emp.emp_name in (select emp_name from table3)

Upvotes: 1

Views: 75

Answers (2)

jpw
jpw

Reputation: 44881

Your query is not correct, given what you want it to do, as the and emp.emp_name in ... part is part of the left join condition and won't limit the rows in table1. This is why you get all rows from table1.

To get all rows in table1 that have matches in table3, plus the salary stuff (or null if missing) from table2 you can either change the second part of the left join condition to a where clause or use an inner join with table3:

-- using a where clause
select emp.emp_name, emp.dept, sal.salary 
from table1 as emp 
left join table2 as sal on emp.emp_name = sal.emp_name 
where emp.emp_name in (select emp_name from table3);

-- or an inner join
select emp.emp_name, emp.dept, sal.salary 
from table1 as emp 
join table3 as t3 on emp.emp_name = t3.emp_name
left join table2 as sal on emp.emp_name = sal.emp_name;

For example if your tables had the following data:

table1: emp1, emp2, emp3
table2: emp1, emp2
table3: emp1, emp3

you would get:

emp_name   salary
emp1       1000
emp3       NULL

Upvotes: 3

Utsav
Utsav

Reputation: 8093

Do Inner Join to get correct result

 select 
   emp.emp_name, 
   emp.dept, 
   sal.salary 
 from table1 as emp 
   inner join table2 as sal on emp.emp_name = sal.emp_name and 
                               emp.emp_name in (select emp_name from table3)

Upvotes: 0

Related Questions