CODEWITHSUNDEEP
sqlsql-server
sakura
sakura

Reputation: 2279

Need Union kind of behavior without using Union of queries/resultset

I have two table one say Employee with columns as

EmpId Name Class Region
 1     rr    x     t
 2     tr    v     g

Another table ConfidentalEmployee with columns as

EmpId(foreign key) Name
 1                  rr

Now have to write query with all the fields of Employee table but those Employee's Id is in ConfidentalEmployee table the details like (Class, Region) for such employee should come as CAN'T DISCLOSE as follows:

EmpId  Name    Class              Region
 1      rr    CAN'T DISCLOSE    CAN'T DISCLOSE
 2      tr       v                  g

I can do it using two queries with join based on EMPIds and perform an union on both the resultset. My query is as follows:

select e.EmpId, e.Name,e.Class,e.Region from Employee e inner join ConfidentalEmployee ce on e.EmpId <> ce.EmpId
UNION
select e.EmpId, e.Name,'CAN'T DISCLOSE' as Class, 'CAN'T DISCLOSE' as Region from Employee e inner join ConfidentalEmployee ce on e.EmpId = ce.EmpId

But am wondering if its possible with a single query without union operation?

Upvotes: 0

Views: 37

Answers (4)

Kiran Hegde
Kiran Hegde

Reputation: 3681

You can try this query

SELECT Emp.EmptId, Emp.Name,
       CASE WHEN CEmp.EmpId IS NULL THEN Emp.Class ELSE 'CAN''T DISCLOSE' END AS Class,
       CASE WHEN CEmp.EmpId IS NULL THEN Emp.Region ELSE 'CAN''T DISCLOSE' END AS Region 
FROM Employee AS Emp
LEFT JOIN ConfidentialEmployee CEmp ON Emp.EmpId = CEmp.EmpId

Upvotes: 2

wiesion
wiesion

Reputation: 2445

If i understand your question right, i would suggest the following:

Left join ConfidentalEmployee on EmpId, in the select statement use CASEs to check if ConfidentalEmployee.EmpId is NULL and alias "CAN'T DISCLOSE" as Class and Region.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You want a join, specifically a left outer join and check for matches:

select e.EmpId, coalesce(ce.Name, e.Name) as Name,
       (case when ce.empid is not null then 'CAN''T DISCLOSE' else e.Class end) as class, 
       (case when ce.empid is not null then 'CAN''T DISCLOSE' else e.Region end) as region
from employee e left outer join
     confidentialemployee ce
     on e.empid = ce.empid;

This is assuming that confidential employees are in both tables, as in the example in your question. Otherwise, union all is the appropriate approach.

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Why are you trying to avoid a UNION? This is what they are made for.

select EmpID
    , Name
    , Class
    , Region
from Employee
union all
select EmpID
    , Name
    , 'Can''t Disclose'
    , 'Can''t Disclose'
from ConfidentialEmployee
order by EmpID

Upvotes: 0

Related Questions