I have SQL test table as below: Item Doc_No Code Line_Item 1 abc1234 101 01 2 abc1234 102 01 3 def5678 101 01 4 def5678 102 01 5 ghi1234 101 01 6 ghi1234 101 02 7 jkl5678 101 01 I am trying to eliminate rows when duplicate values of "Doc_No" has pair values of "101" and "102" in "Code" column

Deleting rows in sql rows based on pair value

I have SQL test table as below:

Item	Doc_No	Code	Line_Item
1	abc1234	101	01
2	abc1234	102	01
3	def5678	101	01
4	def5678	102	01
5	ghi1234	101	01
6	ghi1234	101	02
7	jkl5678	101	01

I am trying to eliminate rows when duplicate values of “Doc_No” has pair values of “101” and “102” in “Code” column e.g abc1234 and def5678.

At the same time I want to maintain duplicate values of “Doc_No” without the pair value of “101” and “102” in “Code” column e.g. ghi1234. Final output as below:

Item	Doc_No	Code	Line_item
5	ghi1234	101	01
6	ghi1234	101	02
7	jkl5678	101	01

I tried to get the rows with duplicate values and exclude them but this will wrongly exclude “ghi1234” and not the final output table I want.

    SELECT 
    a.*,
    b.Count_Item
    FROM dbo.test AS a
    LEFT JOIN 
       (SELECT Doc_No, COUNT(*) AS Count_Item
        FROM dbo.test
        GROUP BY Doc_No
        HAVING COUNT(*) > 1) AS b
    ON a.Doc_No = b.Doc_No
    WHERE b.Count_Item < 2

Item	Doc_No	Code	Line_Item	Count_Item
7	jkl5678	101	01	1

Can somebody help me with this?

Answer

Building on your construct:

SELECT a.*
FROM dbo.test a LEFT JOIN
     (SELECT Doc_No, COUNT(*) AS Count_Item
      FROM dbo.test
      WHERE code IN (101, 102)
      GROUP BY Doc_No
     ) b
     ON a.Doc_No = b.Doc_No
WHERE b.Count_Item < 2 OR Count_Item IS NULL;

Note: This assumes that codes are not duplicated for a doc.

Advertisement

Answer