CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Aditya
Aditya

Reputation: 2311

Exists - Not exists - Exclude records those are having status in 0 ignoring other status associated with that record

Below is my data.

with cte as(
select 'A' name, 0 status
union all select 'A' name, 1 status
union all select 'B' name, 1 status
union all select 'C' name, 2 status
union all select 'D' name, 1 status
)

I want to get only B, C, D as output from the query. Lets say, 0 is status-complete & I want to ignore records associated with it.

This I am able to achieve using the not in clause as below. 

select * from cte c
where c.name not in (select cf.name from cte cf where cf.status=0)

But I want to achieve this using exists or not exists clause in where condition.

Could you please share the logic ?

thanks,

Upvotes: 0

Views: 1226

Answers (4)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14679

DECLARE @tbl1 AS TABLE
(
   Name VARCHAR(50),
   Status INT
)

INSERT INTO @tbl1 VALUES('A',0)
INSERT INTO @tbl1 VALUES('A',1)
INSERT INTO @tbl1 VALUES('B',1)
INSERT INTO @tbl1 VALUES('C',1)
INSERT INTO @tbl1 VALUES('D',1)
INSERT INTO @tbl1 VALUES('E',0)

With Not EXISTS:

SELECT
    *
FROM @tbl1 T1
WHERE NOT EXISTS( SELECT T2.Name FROM @tbl1 T2  WHERE T2.Status=0 AND T1.Name=T2.Name)

With EXISTS:

SELECT
    *
FROM @tbl1 T1
WHERE EXISTS( SELECT T2.Name FROM @tbl1 T2  WHERE  T1.Name=T2.Name AND T1.Status=1  GROUP BY T2.Name having count(T2.Status)=1 )

Output:

enter image description here

Upvotes: 1

Munavvar
Munavvar

Reputation: 821

With NOT EXISTS

with cte as(
select 'A' name, 0 status
union all select 'A' name, 1 status
union all select 'B' name, 1 status
union all select 'C' name, 2 status
union all select 'D' name, 1 status
)
select * from cte out where NOT EXISTS 
(select inn.name from cte inn WHERE out.name = inn.name and inn.status=0)

Upvotes: 1

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

Please try this

with cte as(
select 'A' name, 0 status
union all select 'A' name, 1 status
union all select 'B' name, 1 status
union all select 'C' name, 2 status
union all select 'D' name, 1 status
) 
Select * from cte c
where NOT EXISTS  (select 1 from cte cf where cf.status=0 AND c.name = cf.name)

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

Can you please try with this:

SELECT * FROM cte c
WHERE NOT EXISTS (SELECT cf.name 
                  FROM cte cf WHERE c.name = cf.name AND cf.status = 0)

For this we don't need any column in the where clause because we are addressing that conditional column as comparison in WHERE of sub query.

Upvotes: 1

Related Questions