Hatt_haggerty Hatt_haggerty -4 years ago 110
SQL Question

Find which rows have different values for a given column in SQL

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.

Answer Source

Try this query

    Address A
    INNER JOIN Address B
        ON A.Id = B.Id AND A.[Adress Code] < B.[Adress Code]
    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

    Address A
    INNER JOIN Address B
        ON A.Id = B.Id
    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) .

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download