Monday, May 18, 2020

A Very Unique Case : Delete Duplicate from Cross Referenced Table [Parent Child relationship]

Cross referenced table, i do not know if i am pronounce this is correctly or not. First see the table structure below, and let me know what this table is known as?, how to pronounce it? What is name of structure storing this kind of data.

 XYZ   
 IDCustomerID [Reference to Cus.] RelatedCustomerID [Reference to Cus.]   Relation Ship of Customers
 1 20021 20022 is children of 
 2  20022 20021 is father of
 3  20023 20024 is friend of 
 4  20024 20023 is brother of
 5 20025 20026 is children of


Do not forget to provide your inputs in comment section


Now the case is earlier the application is designed in way to hold the relationship between 2 customers in either way. Now as per new requirement this relationship are duplicate. We need to keep only 1 relation ship from cross reference rows.

example - 
1. Row 1 and Row 2 are duplicate, we need to keep only 1 row (any one)
2. Row 3 and Row 4 are duplicate, we need to keep only 1 row  (any one)
3. Row 5 is itself is unique

So did workaround with friends, of-course nothing is found on internet.google. Tried Differnt type of join , inner join , self join, min , max, dense rank, pivot but nothing works

Below is the query we prepared after all.

SELECT 
    distinct
    CASE
        WHEN CustomerID < RelatedCustomerID  THEN CustomerID 
        ELSE RelatedCustomerID 
        END AS CustomerID_v,
     CASE
        WHEN CustomerID  > RelatedCustomerID  THEN CustomerID 
        ELSE RelatedCustomerID 
        END AS RelatedCustomerID_V
FROM 
    XYZ
 
This could be a good interview question as well

No comments:

Post a Comment

web stats