join 2 different tables to a third table

Question

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. 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,COALES…

Accepted Answer

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.

join 2 different tables to a third table

Solution

Problem