CODEWITHSUNDEEP
sqlmatching
Jenny Foster
Jenny Foster

Reputation: 35

How to find if records match or not

This is probably quite simple i just cant get my head round it.

I want to display a list of the customers that have a different city listed in their billing city versus their customer city. The billing address is in a table titled invoices and the customer address is in a table titled customers.

Is there a function that would show the ones that aren't the same?

This is probably quite simply i just cant get my head round it.

I want to display a list of the customers that have a different city listed in their billing city versus their customer city. The billing address is in a table titled invoices and the customer address is in a table titled customers.

Is there a function that would show the ones that aren't the same?

i can show the two tables together easily using a join but now i want to differentiate them

    select
    c.LastName,
    c.City,
    i.billingCity
    from Customers c
    inner join invoices i on c.customerId = i.customerId

Upvotes: 0

Views: 300

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

One simple option, using an EXISTS clause:

SELECT
    c.LastName,
    c.City
FROM Customers c
WHERE EXISTS (SELECT 1 FROM invoices i
              WHERE i.customerId = c.customerId AND
                    i.billingCity <> c.City);

If you want to also show the other non matching invoice city or cities, then you would need to stick with a join approach:

SELECT
    c.LastName,
    c.City,
    i.billingCity
FROM Customers c
INNER JOIN invoices i
    ON i.customerId = c.customerId AND
       i.billingCity <> c.City;

Your main original problem was omitting a join condition.

Upvotes: 1

Related Questions