CODEWITHSUNDEEP
sqljoin
JD Gamboa
JD Gamboa

Reputation: 382

Unsure what to use in query: Left Join - Inner Join - Where clause

I have, let's say, two tables, employee and salary:

employee                salary
|---------|---------|   |--------|----------|----------|
| ID      | Name    |   | E_ID   | Amount   | Status   |
|-------------------|   |------------------------------|
| 1       | Matt    |   | 1      | 100      | Past     |
| 2       | John    |   | 1      | 120      | Current  |
| 3       | David   |   | 2      | 150      | Current  |
|---------|---------|   |--------|----------|----------|

and I require a join between them that will return me the employee-salary info: null if it's not in the salary table, just one value if it's repeated (the one marked as 'current' in opposition to 'past').

In this example, my required return would be:

|---------|---------------|---------------|
| ID      | Name          | Amount        |
|---------|---------------|---------------|
| 1       | Matt          | 120           |
| 2       | John          | 150           |
| 3       | David         | NULL          |
|---------|---------------|---------------|

I initially tried with a 

WHERE employee.ID=salary.E_ID

but that would leave David out.

I tried a LEFT JOIN

...
employee
LEFT JOIN salary ON employee.ID=salary.E_ID

but that shows Matt twice, and if I add the where clause

...
employee
LEFT JOIN salary ON employee.ID=salary.E_ID
WHERE salary.status='Current'

that leaves David out again.

Not sure how to proceed.

Thank you for your help.

Upvotes: 1

Views: 58

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133370

You should use the condition for salary.status in on clause if you let this condition in where this work as a inner join and don't return the valye for all the employee

select employee.name, salary.amount
from employee
LEFT JOIN salary ON employee.ID=salary.E_ID AND salary.status='Current'

Upvotes: 1

Joe
Joe

Reputation: 626

The given answer by Paparazzi is the way I'd do it too, but purely for information, another way to do this is is like this:

For your last example that ignores David again; if you use WHERE isnull(salary.status, 'Current') ='Current' instead like this:

...
employee
LEFT JOIN salary ON employee.ID=salary.E_ID
WHERE isnull(salary.status, 'Current')='Current'

Then, this will still include him, as we are saying, although the left join is not bringing back anything, treat the null as 'current' anyway, and match the where clause on this.

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

LEFT JOIN salary 
  ON employee.ID = salary.E_ID
 AND salary.status = 'Current'

Upvotes: 4

Related Questions