CODEWITHSUNDEEP
sqlsql-server
AndyKash
AndyKash

Reputation: 57

SQL Query - Eliminate records

The data is stored in the format below, parent code can have zero or more child. The status of Parent and Child is shown. The question is, using SQL query can we eliminate only the parent whose status and child status is closed. For e.g. for the example below only P1 and p4 needs to be eliminated.

ParentCode ChildCode ParentStatus ChildStatus
P1           0         Closed       Closed
P1           C1        Closed       Closed
P2           0         Closed       Closed
P2           C2        Closed       Open
P3           0         Open         Closed
P3           C3        Open         Open
P4           0         Closed       Closed
P5           0         Open         Closed

Upvotes: 0

Views: 60

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think simple aggregation works with this data structure:

select parentcode
from t
group by parentcode
having min(ParentStatus) = max(ParentStatus) and
       min(ParentStatus) = 'Closed' and
       min(ChildStatus) = max(ChildStatus) and
       min(ChildStatus) = 'Closed' ;

Upvotes: 0

Gowtham
Gowtham

Reputation: 11

Use the below query,

select * from Table
where ParentStatus <> 'Closed' OR ChildStatus <> 'Closed'

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

If I understand correctly, You can try to use not exists subquery to eliminated.

SELECT * 
FROM T t1
where not exists (
    select 1 
    from T tt 
    where tt.ParentStatus = t1.ParentStatus and tt.ParentStatus = 'Closed'
    and tt.ChildStatus = t1.ChildStatus and tt.ChildStatus = 'Closed'
)

sqlfiddle

EDIT

You can try to use CTE with distinct to make your expectation from your comment,

;WITH CTE AS (
    SELECT * 
    FROM T t1
    where not exists (
        select 1 
        from T tt 
        where tt.ParentStatus = t1.ParentStatus and tt.ParentStatus = 'Closed'
        and tt.ChildStatus = t1.ChildStatus and tt.ChildStatus = 'Closed'
    )
),CTE2 AS (
    SELECT DISTINCT code
    FROM (
        SELECT ChildCode code
        FROM CTE 
        UNION ALL
        SELECT ParentCode
        FROM CTE 
    ) t1
    WHERE code <> '0'
)
SELECT * 
FROM CTE2

sqlfiddle

Upvotes: 1

Related Questions