Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

OUTER JOIN and EXISTS condition

Posted on 2014-03-06
5
Medium Priority
?
264 Views
Last Modified: 2014-03-22
Hello,

 Table Structure
The embedded image in this post has sample table structure and data related to my question.
Please refer to it.

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
Comment
Question by:sath350163
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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   )

Open in new window

0
 

Author Comment

by:sath350163
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

by:sath350163
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) 

Open in new window


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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 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)  

Open in new window


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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question