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
I'm just trying to figure out whether the address for each ID matches. So in this case I want to return just ID 3 as having a different address for Address Code 1 and 2.
Join the table with itself and give it two different aliases (A and B in the following example). This allows to compare different rows of the same table.
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
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) .
You can do this using a group by:
select id, addressCode
from t
group by id, addressCode
having min(address) <> max(address)
Another way of writing this may seem clearer, but does not perform as well:
select id, addressCode
from t
group by id, addressCode
having count(distinct address) > 1
Personally, I would print them to a file using Perl or Python in the format
<COL_NAME>: <COL_VAL>
for each row so that the file has as many lines as there are columns. Then I'd do a diff between the two files, assuming you are on Unix or compare them using some equivalent utilty on another OS. If you have multiple recordsets (i.e. more than one row), I would prepend to each file row and then the file would have NUM_DB_ROWS * NUM_COLS lines
This works for PL/SQL:
select count(*), id,address from table group by id,address having count(*)<2
Source: Stackoverflow.com