CODEWITHSUNDEEP
mysqlsqljoin
mehere
mehere

Reputation: 1556

join 2 different tables to a third table

I have an sql issue. I have 3 tables like in the image. In the front end (User Interface), I have a selectone box to select a course and an employee autocomplete. The autocomplete must retrieve all employee names along with the status for the selected course.

enter image description here

I tried 

select e.id,per.id,t.status 
from employee e 
join person per on e.personId=per.id 
left join training t on e.id=t.employeeId`

but this retrieves duplicate rows for the employeeId '1'. for the employee with id 1, I need to retireve only the row with the selected courseId (selected from User Interface. )

In short,I need all employees information plus the selected courses employee info and also empIds must not repeat.

If selected course id is 34, the retrieved output must contain

Empid,PersonName,Status
1, Ravi , 1; 
2, Meera , 0; 
3, Rahul ,0; 
4, Vinu, 0.

How do i form the reqd sql query?

As per suggestion provided, I sort of modified the accepted answer to (as per my requirement)

SELECT e.id,per.name,COALESCE(t.status,0)
FROM employee e 
JOIN person per ON e.personId=per.id 
LEFT JOIN training t ON e.id=t.employeeId
    AND t.courseId = ?

Upvotes: 4

Views: 109

Answers (1)

Bohemian
Bohemian

Reputation: 424993

The trick with left joins is to add the condition on the left-joined table to the join condition:

select e.id,per.id,t.status 
from employee e 
join person per on e.personId=per.id 
left join training t on e.id=t.employeeId
    and t.courseId = ?

This only attempts joins to specific training rows.

If you put the course condition into a where clause, you lose the left join - it effectively becomes an inner join, because where clause conditions are executed after the join is made. Conditions in the join condition however are executed as the join is made.

As a general comment, many people don't realise that you can put non-key conditions into a join condition. In fact, as in this situation, it is the cleanest way to achive the output you want.

Upvotes: 5

Related Questions