CODEWITHSUNDEEP
sqlsql-server
BobSki
BobSki

Reputation: 1552

Join on two tables - display data where there is no matching record

I have two tables that I'm trying to do a join on....the structure of my tables looks like this...

tbl1
NoClient          ApptDate         Status         
1234              12/22/2016       A
3555              1/23/2017        A
3495              12/23/2016       A


tbl2
NoClient          ApptDate  
1234              12/22/2016
3555              1/24/2017

So I'm trying to join these two tables on NoClient and ApptDate.

My goal is to see all records from tbl1 - where there is no matching record in tbl2 (ApptDate and Noclient)

Desired end result in this case

NoClient      AppDate
3555          1/23/2017        
3495          12/23/2016

NoClient = 3555 has a corresponding record in tbl2 however, the ApptDate is not the same. NoClient = 3495 doesn't have a corresponding record all together.

Upvotes: 0

Views: 60

Answers (2)

Winters
Winters

Reputation: 11

I think this is what you are looking for.

SELECT * FROM Table1

WHERE (NOT EXISTS (SELECT * FROM Table2

WHERE(Table1.NoClient = Table2.NoClient) AND (Table1.ApptDate = Table2.ApptDate )))

Upvotes: 1

Siyual
Siyual

Reputation: 16917

You can use a LEFT OUTER JOIN for this:

Select      T1.NoClient, T1.ApptDate
From        Tbl1    T1
Left Join   Tbl2    T2  On  T1.NoClient = T2.NoClient
                        And T1.ApptDate = T2.ApptDate
Where       T2.NoClient Is Null

Alternatively, you can also use a WHERE NOT EXISTS:

Select  T1.NoClient, T1.ApptDate
From    Tbl1    T1
Where Not Exists
(
    Select  *
    From    Tbl2    T2
    Where   T1.NoClient = T2.NoClient
    And     T1.ApptDate = T2.ApptDate
)

Upvotes: 2

Related Questions