Query To Compare Data

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.

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.

StackExchange Database Administrators Q#218026, answer score: 2