Joing two tables showing me records from one tables where it doesn't have a corresponding value in another table

Question

So I have two tables that I'm trying to do a join on showing me records in one table that don't have corresponding values in another table....

tbl1

ClientNo      DateRec       CompID
123           1/2/2017      5558
124           1/3/2017      5558
234           1/3/2017      5558


tbl2
ClientNO      CompID
123           5558
124           5558

So eveerything here is based on CompID = 5558. This is the magic number. I'm looking to join both tables and only show me records from tbl1 - where tbl2 does not have a record for the same clientID and 5558.

Desired end result:

 ClientNo      DateRec       CompID
 234           1/3/2017      5558

This is my desired end result because client 234 does not have the same CompID in tbl2. So my goes is only show me records from tbl1 where tbl2 doesn't have the same CompID (in this case 5558)

Answer 1

You have pretty much described not exists:

select t.*
from tbl1 t
where not exists (select 1 from tbl2 t2 where t2.ClientNo = t.ClientNo and t2.CompId = t.CompId);