CODEWITHSUNDEEP
sql
Srinivasan Rajagopal
Srinivasan Rajagopal

Reputation: 19

Exclude rows if column values are equal of same table

 ![TABLE][1]

Hello All,

I have a table with above records where you can 2 entries for each N_ID. I would like to get the records from this only if below condition is satisfied.

Say for example

Status column value is 1 & 2 for N_ID =2 and 2 & 1 for N_ID=5 which means status value is different(i.e Both 1 & 2).

But if you see N_ID=3, Status column has 1 & 1 which is same.

So i want the records excluding N_ID which has same status value(i.e Which has 1 & 1 or 2 & 2 and so on).

In above case, i want only the records with N_ID=2,5.

thanks

Upvotes: 1

Views: 6272

Answers (3)

A Hocevar
A Hocevar

Reputation: 726

You can exclude the rows where you have more than one occurrence of the same status per ID

 SELECT * 
   FROM TABLENAME tb 
   WHERE tb.N_ID NOT IN (
     SELECT tb.N_ID 
       FROM TABLENAME tb
       GROUP BY tb.N_ID, tb.CONFIG_TYPE, tb.STATUS
       HAVING COUNT(*) > 1)

Table

Upvotes: 1

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Try option with EXISTS() and check COUNT(*)

SELECT *
FROM dbo.test16 t
WHERE t.Config_Type != 2 AND EXISTS (
              SELECT 1
              FROM dbo.test16 t2
              WHERE t.Networkelemenid = t2.Networkelemenid                
              GROUP BY t2.Networkelemenid, t2.Config_Type
              HAVING COUNT(DISTINCT t2.Status) > 1
              )

This script grouped data on t2.Config_Type. HAVING COUNT(DISTINCT t2.Status) specifies that only unique rows can appear in the result set.(e.g. 1,2 = 2; 1,1 or 2,2 = 1)

For second condition you need this script

SELECT *
FROM dbo.test41 t
WHERE t.Config_Type != 2 AND EXISTS (
              SELECT 1
              FROM dbo.test41 t2
              WHERE t.Networkelemenid = t2.Networkelemenid                
              GROUP BY t2.Networkelemenid, t2.Config_Type, t2.Status
              HAVING COUNT(t2.Status) > 1
              )

Table

TABLE2

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460138

You can use EXISTS

SELECT * FROM dbo.TableName t1
WHERE EXISTS(
    SELECT 1 FROM dbo.TableName t2
    WHERE t1.N_ID = t2.N_ID
    AND   t1.Status <> t2.Status
)

Upvotes: 1

Related Questions