CODEWITHSUNDEEP
sqlsql-server
PriceCheaperton
PriceCheaperton

Reputation: 5349

comparing fields in 2 different tables using SQL

I would like to compare if the address fields in contact table are different to that of the delivery table.

SELECT contactID, addressline1
FROM contact
where contactID = '0018319'

Below is the delivery table which contains the old details.

SELECT contactID, addressline1
FROM delivery
where contactID = '0018319'

Upvotes: 0

Views: 129

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If you want to return a flag, then you would use case in the select statement:

select contactId,
       (case when d.addressline1 = c.addressline1 or d.addressline1 is null and c.addressline1 is null
             then 'SAME'
             else 'DIFFERENT'
        end) as SameOrDifferent
from contact c join
     delivery d
     on c.contactId = d.contactId
where contactId = '0018319';

This is going to compare each address in the two tables.

If you want to know if all are the same, then the query is more complicated. The idea is to do a full outer join between the two table (for the given contractid) on addressline1. If all the addresslines match, then the full outer join will never produce NULL values. If any are missing (on either side), then there will be NULL values.

select coalesce(c.contactId, d.contactId) as contactId,
       (case when sum(case when c.addressline1 is null or d.addressline1 is null
                           then 1
                           else 0
                       end) = 0
             then 'SAME'
             else 'DIFFERENT'
        end) as SameOrDifferent
from (select c.* from contact c where c.contactId = '0018319') c full outer join
     (select d.* from delivery d where d.contactId = '0018319') d
     on c.addressline1 = d.addressline1 and
        c.contactId = d.contactId  -- not actually necessary but useful for multiple contacts
group by coalesce(c.contactId, d.contactId)

Upvotes: 0

Olle Johansson
Olle Johansson

Reputation: 518

SELECT contactID, d.addressline1, c.addressline1
FROM delivery d
INNER JOIN contact c on d.contactID = c.contactID
where d.addressline1 != c.addressline1

Upvotes: 2

Related Questions