Exclude row based in multiple column value-CodePudding

i need to exclude rows based multiple column conditions like 1) first find value "rev" from column ID1, 2) find the corresponding value in column ID2 based on 1st condition i.e. Ref1, and exclude all the rows contains the value in the 2nd column.

    ID1     ID2     VALUE
    post    Ref1    12
    post    Ref1    3
    rev     Ref1    12
    rev     Ref1    3
    post    Ref2    10
    post    Ref2    5

output

    ID1     ID2     VALUE
    post    Ref2    10
    post    Ref2    5

CodePudding user response：

This assumes that "rev" stands for reversal or something similar, and that if the ID2 and VALUE are the same for a "post" and a "rev" they negate each other.

I created your table with this:

DROP TABLE IF EXISTS #table1;
CREATE TABLE #Table1 (ID1 VARCHAR(4), ID2 VARCHAR(4), VALUE INT);
INSERT INTO #Table1 (ID1, ID2, VALUE)
VALUES ('post', 'Ref1', 12),
       ('post', 'Ref1', 3),
       ('rev', 'Ref1', 12),
       ('rev', 'Ref1', 3),
       ('post', 'Ref2', 10),
       ('post', 'Ref2', 5);

I've then written this query to initially fetch all the 'post' rows of data, then self join matching on ID2 and VALUE and that ID1 now has to be 'rev', then in the WHERE clause adding something to only return rows where there is no T2.ID1

SELECT T1.ID1,
       T1.ID2,
       T1.VALUE
FROM #Table1 AS T1
     LEFT JOIN #Table1 AS T2 ON T2.ID2 = T1.ID2 AND T2.VALUE = T1.VALUE AND T2.ID1 = 'rev'
WHERE T1.ID1 = 'post' AND T2.ID1 IS NULL;

CodePudding user response：

You can first select all rows having ID1 = 'post' then remove all records having ID1 = 'rev', when 'rev' is replaced with 'post'.

SELECT * FROM tab WHERE [ID1] = 'post'
EXCEPT
SELECT 'post' AS [ID1], [ID2], [VALUE] FROM tab WHERE [ID1] = 'rev'

Check the demo here.