zimmer9
asked on
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
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
ASKER
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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Could it also be due to 2 matching records in ztbl_Master_Template for 1 record
ztbl_Source_SSFIII ?
ztbl_Source_SSFIII ?
yes, possibly.
Open in new window