Solved

# OUTER JOIN and EXISTS condition

Posted on 2014-03-06
257 Views
Hello,

The embedded image in this post has sample table structure and data related to my question.

Criteria
I want employee rec to be returned in the output if it satisfies any of the below 3 criterias:
If Employee's salary is > 0
or
If Employee exist in the EmployeeStatus table with EmpStatus as 'Active'
or
If Employee does not exist in the EmployeeStatus table

I should not get Employee = 4 in output as Kate's salary is 0, and although exist in the EmployeeStatus table, the EmpStatus is 'Inactive'

Question:

I am trying to achieve this using EXISTS clause, but the problem is that when I use EXISTS, only Employee record that also exists in the EmployeeStatus gets returned.

Can you please show me how to achieve this using EXISTS approach, OUTER JOIN Approach, as well as anyother approach that would easily solve this problem.

Thanks!
0
Question by:sath350163
• 3
• 2

LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 39911702
this approach should do the job:
``````select *
from employee e
WHERE e.Salary > 0
OR EXISTS ( select NULL FROM EmployeeStatus es where es.employeeid = e.id and es.EmpStatus as 'Active' )
OR NOT EXISTS ( select NULL FROM EmployeeStatus es where es.employeeid = e.id   )
``````
0

Author Comment

ID: 39911735
Thank you so much for the response.

Can you also show me how to do this using CROSS APPLY (if its doable) and using OUTER JOIN?

Thanks!
0

Author Comment

ID: 39911756
Would this approach work?

``````SELECT *
FROM Employee e LEFT OUTER JOIN EmployeeStatus es
ON e.Eno = es.Eno
AND (e.Salary > 0.00 OR es.EmpStatus = 'ACTIVE' OR es.EmpStatus IS NULL)
``````

When I execute this sql, I get the Eno = 4 also in the result, along with others.

But Eno = 4 has salary =0 and the EmpStatus is 'INACTIVE' in EmpStatus table,
So technically this Employee record should not be returned in the resultset.

What is the problem here?
Is sql server applying the condition for merging the records between the two tables, and not for filtering?

Thanks!
0

LVL 142

Expert Comment

ID: 39911844
CROSS APPLY is not usable here ....

I would have said that your LEFT JOIN should work, but visible it does not ...
but I wonder why.
I have tested that my syntax does work ...

let me check if I find something on this ...
0

LVL 142

Assisted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 39911846
this works again:
``````SELECT sq.*
FROM (Select e.*, es.EmpStatus
FROM Employee e
LEFT OUTER JOIN EmployeeStatus es
ON e.Eno = es.Eno
) sq
WHERE (sq.Salary > 0.00 OR sq.EmpStatus = 'ACTIVE' OR sq.EmpStatus IS NULL)
``````

visibly, the condition on EmpStatus here is not moved to the OUTER JOIN as in your code, but I couldn't see the actual technical reason.
0

## Featured Post

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.