I am trying to compare two addresses from the same ID to see whether they match. For example:
Id Adress Code Address
1 1 123 Main
1 2 123 Main
2 1 456 Wall
2 2 456 Wall
3 1 789 Right
3 2 100 Left
Try this query
SELECT DISTINCT A.Id FROM Address A INNER JOIN Address B ON A.Id = B.Id AND A.[Adress Code] < B.[Adress Code] WHERE A.Address <> B.Address
It joins the table with itself and gives it two different aliases (A and B). This allows to compare different rows of the same table.
The "less than" comparison
< ensures that you get 2 different addresses and you don't get the same 2 address codes twice. Using "not equal"
<> instead, would yield the codes as (1, 2) and (2, 1); each one of them for the
A alias and the
B alias in turn.
The join clause is responsible for the pairing of the rows where as the where-clause tests additional conditions.
The query above works with any address codes. If you want to compare addresses with specific address codes, you can change the query to
SELECT A.Id FROM Address A INNER JOIN Address B ON A.Id = B.Id WHERE A.[Adress Code] = 1 AND B.[Adress Code] = 2 AND A.Address <> B.Address
I imagine that this might be useful to find customers having a billing address (Adress Code = 1 as an example) differing from the delivery address (Adress Code = 2) .