3

I'm trying to build a query to show rows in a Customer table that match rows exactly in 2 columns (Address 1 and Address 2) but then show only if a 3rd column is not matching.

Customer ID Status  Address 1 Address 2
----------  ------ ---------- ----------
    2        Free   1 Main St. Apt 2
    3        Paid   1 Main St. Apt 2
    5        Free   2 Main St. Apt 1
    7        Free   2 Main St. Apt 1

So in the above data example I would want the query to only show that Customer IDs 2 and 3 match on Address 1 and Address 2 but do not match by Status. It would be like an exception report showing those data matches. Hope this makes sense.

  • Microsoft SQL Server Management Studio 14.0.17277.0. – Rykoff Sexton Sep 19 '18 at 15:11
2

You can join the table with itself:

SELECT c1.CustomerID, c1.Status, c2.CustomerID, c2.Status
  FROM Customers AS c1
  INNER JOIN Customers AS c2
    ON c1.Address1   =  c2.Address1
  AND (c1.Address2   =  c2.Address2 OR (c1.Address2 IS NULL AND
                                        c2.Address2 IS NULL))
   AND c1.Status     != c2.Status
   AND c1.CustomerID <  c2.CustomerID

The last line makes sure you don't get duplicate rows (unless there are more than two customers on the same address).

As suggested in the comments by Nick Roz, some fields could be NULL. Since NULL != NULL, you need to check specifically for those cases. In the query, I assumed CustomerID, Status and Address1 are never NULL, but Address2 can be.

2

I think EXISTS is the simplest solution, since it handles NULL values correctly and the query plan is a simple semi-join.

DECLARE @tmp TABLE
      (
          CustomerID INT,
          Status VARCHAR(50),
          Address1 VARCHAR(50),
          Address2 VARCHAR(50)
      );

INSERT INTO @tmp
VALUES ( 2, 'Free', '1 Main St.', 'Apt 2' ),
       ( 3, 'Paid', '1 Main St.', 'Apt 2' ),
       ( 5, 'Free', '2 Main St.', 'Apt 1' ),
       ( 7, 'Free', '2 Main St.', 'Apt 1' );


SELECT *
FROM @tmp AS t
WHERE EXISTS 
(
SELECT 1/0
FROM @tmp AS t2
WHERE t.Address1 = t2.Address1
AND   t.Address2 = t2.Address2
AND   t.Status <> t2.Status
)
0

You can identify different status values using group by on Address1 and Address2 columns and checking if max and min values for status are different.

Then you can join this dataset to the original data to retrieve only the lines that have same Address1, Address2, but different status.

declare @tmp table(CustomerID int, [Status] varchar(50), 
                   Address1 varchar(50), Address2 varchar(50))

insert into @tmp values
     (2, 'Free','1 Main St.','Apt 2')
    ,(3, 'Paid','1 Main St.','Apt 2')
    ,(5, 'Free','2 Main St.','Apt 1')
    ,(7, 'Free','2 Main St.','Apt 1')

select t1.* 
from @tmp t1
inner join 
(
    select Address1, Address2
    from @tmp 
    group by Address1, Address2
    having min(isnull([Status],'')) <> max(isnull([Status],''))
) err
on t1.Address1 = err.Address1 
    and 
   t1.Address2 = err.Address2

Results:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.