CODEWITHSUNDEEP
sql-server
Henrik
Henrik

Reputation: 1807

Get records where one column is same but another column is different

I'm trying to compare two "lists" in same table and get records where customerId column has the same value but storeid is different.

Lists (table definition)

name             listid   storeid   customerid

ComparingList1     1        10         100
ComparingList1     1        10         101
ComparingList1     1        11         100
ComparingList1     1        11         102
ComparingList1     1        11         103
ComparingList1     1        11         104

ComparingList2     2        10         100
ComparingList2     2        10         101
ComparingList2     2        11         100
ComparingList2     2        11         102
ComparingList2     2        11         103
ComparingList2     2        12         104
ComparingList2     2        12         105

Query

SELECT 
   ComparingList2.customerid as customerId,
   ComparingList1.storeid as expectedStoreId, 
   ComparingList2.storeid as actualStoreId
FROM Lists ComparingList2
LEFT JOIN Lists ComparingList1
   on ComparingList1.customerid = ComparingList2.customerid
WHERE 
   ComparingList1.listid = 1
   AND ComparingList2.listid = 2
   AND ComparingList2.storeid <> ComparingList1.storeid

This query gives me:

customerid   expectedStoreId   actualStoreId
   100             10                 11 
   100             11                 10 
   104             11                 12

But the result i want is:

customerid         expectedStoreId      actualStoreId
   104                   11                   12

Fiddle

Upvotes: 0

Views: 95

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

By removing the matches, you can join the remaining values on customerid like this:

;WITH list1 as
(
  SELECT storeid, customerid 
  FROM Lists
  WHERE listid = 1
  EXCEPT 
  SELECT storeid, customerid
  FROM Lists
  WHERE listid = 2
), list2 as
(
  SELECT storeid, customerid 
  FROM Lists
  WHERE listid = 2
  EXCEPT 
  SELECT storeid, customerid 
  FROM Lists
  WHERE listid = 1
)
SELECT 
  list1.customerid, 
  list1.storeid ExpectedStoreId,
  list2.storeid ActualStoreId
FROM list1 
JOIN list2 
ON list1.customerid= list2.customerid

Upvotes: 3

ATC
ATC

Reputation: 957

Well, you are close. I've added another condition to your where clause that gives the desired result, However I'm not sure how it will perform on actual data.

Here is the modified query:

SELECT 
  ComparingList2.customerid as customerId,
  ComparingList1.storeid as expectedStoreId, 
  ComparingList2.storeid as actualStoreId
FROM Lists ComparingList2
LEFT JOIN Lists ComparingList1
  on ComparingList1.customerid = ComparingList2.customerid 
WHERE ComparingList1.listid = 1
AND ComparingList2.listid = 2
AND ComparingList2.storeid <> ComparingList1.storeid
AND NOT EXISTS -- Added this part
(
    SELECT 1 
    FROM Lists c
    WHERE ComparingList1.customerid = c.customerid
    AND ComparingList1.storeid = c.storeid
    AND c.listid = 2
)

Upvotes: 4

Related Questions