How would you write a query to find the records in 1 table that don't exist in another table ?

I am developing an Access application using Access as the front end and SQL Server as the back end database. I use the following query to yield a result set of matching records.

How would you rewrite the following query to:
 
find the records in ztbl_Source_SSFIII THAT DO NOT EXIST in ztbl_Master_Template
-----------------------------------------------------------------------------------------------------
SELECT a.id, a.[Fund Name], b.[Investor Name], b.[Legal Entity]
FROM ztbl_Master_Template a
INNER JOIN ztbl_Source_SSFIII b ON b.[Investor Name] = a.[Account Name]  AND
a.[Legal Entity] LIKE '%' + b.[Legal Entity] + '%'
where [Fund Name] = 'Special Situations Fund III'
order by id
zimmer9Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
that's usually a NULL value in either legal entity or fund name?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will do:
SELECT a.id, a.[Fund Name], b.[Investor Name], b.[Legal Entity]
FROM ztbl_Source_SSFIII b 
LEFT JOIN ztbl_Master_Template a
  ON b.[Investor Name] = a.[Account Name]  
  AND a.[Legal Entity] LIKE '%' + b.[Legal Entity] + '%' 
  AND a.[Fund Name] = 'Special Situations Fund III'
WHERE a.id IS NULL

Open in new window

0
 
zimmer9Author Commented:
If I perform the following query:

select count(*) from ztbl_Source_SSFIII

the result of the record count is 966 rows.
---------------------------------------------------
As a result of the following query, the result set is 724 rows:

SELECT a.id, a.[Fund Name], b.[Investor Name], b.[Legal Entity]
FROM ztbl_Master_Template a
INNER JOIN ztbl_Source_SSFIII b ON b.[Investor Name] = a.[Account Name]  AND
a.[Legal Entity] LIKE '%' + b.[Legal Entity] + '%'
where [Fund Name] = 'Special Situations Fund III'
order by id
-----------------------------------------------------------------

As a result of the following query, the result set is 243 rows:

SELECT a.id, a.[Fund Name], b.[Investor Name], b.[Legal Entity]
FROM ztbl_Source_SSFIII b
LEFT JOIN ztbl_Master_Template a
  ON b.[Investor Name] = a.[Account Name]  
  AND a.[Legal Entity] LIKE '%' + b.[Legal Entity] + '%'
  AND a.[Fund Name] = 'Special Situations Fund III'
WHERE a.id IS NULL
----------------------------------------------------------------
Thus, the record count of 966 rows is 1 less than the result of the 2 queries (724 rows + 243 rows).

Any thoughts on how I can reconcile the 1 record difference ?

0
 
zimmer9Author Commented:
Could it also be due to 2 matching records in ztbl_Master_Template for 1 record
ztbl_Source_SSFIII ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, possibly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.