CODEWITHSUNDEEP
sqlsql-serverselect
Anjie
Anjie

Reputation: 21

Find records that exists in one table but not another based on multiple columns

I have two tables, billing and weekly.
I need to find values that are in the billing table but not in the weekly table, but I need to base it off of more than one column.
I have a query that works for just one column:

SELECT * from billing
outer apply
(select * from weekly where billing.[email]= weekly.[email])a
where a.[email] is null

Sometimes in my data the email can change.
So I need to add something if the email doesn't match, check first and last name but do not know how to do this.

Upvotes: 1

Views: 2414

Answers (2)

FutbolFan
FutbolFan

Reputation: 13713

You can use NOT EXISTS operator to exclude any records that match in weekly based on email OR first and last name. 

SELECT bl.*
        FROM billing bl
        WHERE NOT EXISTS (
                SELECT 1
                FROM weekly wk
                WHERE (bl.[email] = wk.[email]
                    OR (
                        bl.firstName = wk.firstName
                        AND bl.lastName = wk.lastName))
                    AND bl.lenderName <> wk.lenderName --added this check
                )

Upvotes: 0

Mureinik
Mureinik

Reputation: 310993

You could use the exists operator:

SELECT * 
FROM   billing
WHERE  NOT EXISTS
       (SELECT * 
        FROM   weekly 
        WHERE  billing.[email] = weekly.[email] OR
               (billing.[firstName] = weekly.[firstName] AND
                billing.[lastName] = weekly.[lastName]
               )
       )

Upvotes: 2

Related Questions