dotexecutable dotexecutable - 1 year ago 63
SQL Question

Can't find missing matching rows in SQL join

Quite new to SQL so please be patient. I have two tables, one is a Locations table and one is a FileImport table. I get files daily and I then import them they then get sorted based on the Locations in the Locations table. My usual method of tracking the missed ones are done via Excel but there must be a simpler way through SQL. I currently have the following query:

SELECT dbo.Location.LocationDesc, dbo.FileImport.LocationName, dbo.FileImport.CreatedDate, dbo.FileImport.FileName
FROM dbo.FileImport left OUTER JOIN
dbo.Location ON dbo.FileImport.LocationName = dbo.Location.LocationDesc
where CAST(dbo.FileImport.CreatedDate AS Date) = '2016-09-14'

There are some older Locations that are not used within the Locations table but for now I want to find all Locations in the Locations table which don't have a corresponding upload for that day, any help will be appreciated.


Answer Source

This'll give you all locations that dont have an file import on 14-Sep-2016

SELECT loc.LocationDesc
dbo.Location loc
where not exists 
(select null from 
dbo.FileImport fi
where fi.LocationName = loc.LocationDesc
and CAST(dbo.FileImport.CreatedDate AS Date) = '2016-09-14');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download