CODEWITHSUNDEEP
sqlms-access
WiscoRadio
WiscoRadio

Reputation: 174

Duplicate Rows when self joining tables in SQL

I am trying to self join a table together based on the column "Warehouse Number". The goal is to list part numbers, descriptions, and item class of any pairs of parts that are in the same item class and same warehouse. Below is an example of the desired output and starting data.

STARTING DATA

Starting Data

EXAMPLE OF SOME DESIRED DATA

desired output

However, when that self join happens, there aren't "exact" duplicates but the pairs appear twice in the table.

EXAMPLE OF OUTPUT WITH PROBLEMS (HIGHLIGHTED) PROBLEMS

I have tried most iterations of UNION, INNER JOIN, and other join methods. Is it possible to remove the pairs since it isn't technically an exact duplicate of another row?

Current SQL Code enter image description here

Upvotes: 1

Views: 1081

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

You may alter your join condition to check that the first part number is strictly less than the second one:

SELECT
    t1.PARTNUMB, t1.PARTDESC, t1.ITEMCLSS, t2.PARTNUMB, t2.PARTDESC, t2.ITEMCLSS
FROM PARTFIRST t1
INNER JOIN PARTSECOND t2
    ON t1.WRHSNUMB = t2.WRHSNUMB AND
       t1.ITEMCLSS = t2.ITEMCLSS AND
       t1.PARTNUMB < t2.PARTNUMB;

The problem with using FIRST.PARTNUMB <> SECOND.PARTNUMB is that it would report two different part numbers twice, once on the left/right side and vice-versa. By using a strictly less than inequality, we exclude "duplicates," as you view them.

Upvotes: 2

Related Questions