sql query question

Question

Can someone pls tell me how to get below output using subquery

]1

Answer 1

SELECT Emp, Salary, reverse, type
FROM   tbl
WHERE  NOT (Emp = 1 AND revers = 1)

Or is there any particular business logic involved?

Answer 2

Hi @deepika omer ,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Furthermore, it helps to have a short description of the business rules whih explains why you want that result.

Suppose you would like to filter out all the rows like below:

1. if one emp has both 1 and 0 as revers, then choose all rows where revers=0
2. if one emp has only one revers either 0 or 1, remain it.

Please refer below and check whether it is helpful.

create table tablede  
(Emp int,  
salary int,  
revers bit,  
type int)  
  
insert into tablede values  
(101,1000,0,40),  
(101,1000,1,40),  
(101,1000,0,40),  
(102,2000,1,40),  
(103,3000,0,40)  

select  a.*   
from tablede a   
inner join   
(select emp, count(revers) count from tablede group by emp) b   
on a.Emp=b.Emp  
where count=1 or (count>1 and revers=0)  

Output:

Emp	salary	revers	type  
101	1000	0	40  
101	1000	0	40  
102	2000	1	40  
103	3000	0	40  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.