CODEWITHSUNDEEP
sqlsql-servert-sql
rickyProgrammer
rickyProgrammer

Reputation: 1167

SQL query to compare mismatch records

I need to compare records of USA from UK.

UK records on Barcode and Warehouse fields are the pattern to be followed or copied by UK.

After setting up records of UK per item group of UK, I like to check by writing a query which item groups are mismatch records as compare to that of USA

Please see sample table illustration below.

Country Item Group  Barcode     Warehouse
------------------------------------------
UK      Clothes     Standard    A
USA     Clothes     Standard    B
UK      Gift        Standard    A
USA     Gift        Standard    A
UK      Shoes       Standard    A
USA     Shoes       EAN         B

I write this code, but this list all records and I need to manually check which are mismatch (if there are thousands of records, it would be harder). 

 SELECT 
     a.country, b.category, a.barcode, a.warehouse
 FROM 
     Retail a 
 INNER JOIN 
     Category b on a.ID = b.category
 WHERE 
     a.country IN ('USA', 'UK')
 ORDER BY 
     b.category,

I'd like to modify this by writing a SQL query that will only show the mismatch records. Expected result set output should be this

Country Item Group  Barcode     Warehouse
-----------------------------------------
UK      Clothes     Standard    A
USA     Clothes     Standard    B
UK      Shoes       Standard    A
USA     Shoes       EAN         B

Since Clothes Item Group is mismatch (Warehouse) and Shoes also (Barcode, Warehouse)

Upvotes: 0

Views: 967

Answers (4)

HJB
HJB

Reputation: 1

WITH data AS (
    SELECT a.country, b.category, a.barcode, a.warehouse
    FROM Retail a INNER JOIN Category b 
        ON a.ID = b.category
    WHERE a.country IN('USA', 'UK')
)

SELECT T1.* 
FROM data AS T1
INNER JOIN data AS T2
    ON T1.ITEM_GROUP = T2.ITEM_GROUP
    AND (T1.BARCODE   <> T2.BARCODE 
      OR T1.WAREHOUSE <> T2.WAREHOUSE)
WHERE (T1.COUNTRY = 'UK'  AND T2.COUNTRY = 'USA')
   OR (T1.COUNTRY = 'USA' AND T2.COUNTRY = 'UK' )
;

Upvotes: 0

LoztInSpace
LoztInSpace

Reputation: 5697

    with d as 
    (
    SELECT a.country, b.category, a.barcode, a.warehouse
     FROM Retail a INNER JOIN Category b on a.ID = b.category
     WHERE a.country in ('USA', 'UK')
    )
    select d1.* from d as d1 inner join
    (
    select a.country,  category
    from d
    group by
    a.country, category
    having count(distinct barcode)>1 or count(distinct warehouse)>1
    ) d2
    on d1.country=d2.country and d1.category=d2.category
    order by
    d1.country=d2.country

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Is this what you want?

select r.*
from retail r
where (r.country ='USA' and
       exists (select 1
               from retail r2
               where r2.country = 'UK' and
                     r2.itemgroup = r.itemgroup and
                     (r2.barcode <> r.barcode or
                      r2.warehouse <> r.warehouse
                     )
              )
       ) or
       (r.country ='UK' and
       exists (select 1
               from retail r2
               where r2.country = 'USA' and
                     r2.itemgroup = r.itemgroup and
                     (r2.barcode <> r.barcode or
                      r2.warehouse <> r.warehouse
                     )
              )
       ) ;

Upvotes: 2

D-Shih
D-Shih

Reputation: 46239

You can try to use COUNT window function in subquery

SELECT *
FROM (
    SELECT a.country, 
           b.category, 
           a.barcode, 
           a.warehouse,
           COUNT(*) OVER(PARTITION BY b.category,a.Warehouse ORDER BY category) cnt
    FROM Retail a INNER JOIN Category b on a.ID = b.category
    WHERE a.country in ('USA', 'UK')
) t1
WHERE t1.cnt = 1

Upvotes: 4

Related Questions