Running A Report On Missing File; Getting Duplicates

[arnolds_trd]

I am trying to run a report that lets me know when a cert file is missing from a group of PC's with certain software. I am able to get results, but I realize that some of the results are duplicates due to it finding the file in the program files (x86) folder but not in the primary program files folder. Is there any way to reduce this down if the file is found in one location but not the other.

Here is the code that I am using:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName,
tblFileVersions.FilePathfull,
Case
When tblFileVersions.Found Is Null Then 'Not checked'
When tblFileVersions.Found = 0 Then 'Not found'
Else 'Found'
End As FilePresent
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Where tblSoftwareUni.softwareName Like 'GSG Windows Application' And
tblFileVersions.FilePathfull Like '%certadddate.txt%' And
(tblFileVersions.Found Is Null Or tblFileVersions.Found = 0)
Order By tblAssets.AssetName

Thank you

[Jelly]

Subqueries should be useful for this. Code is commented, it will no longer show systems that have the cert in at least one of the filepaths. Sounded like that should be fine.

Select Top 1000000 
	tblAssets.AssetID,
	tblAssets.AssetName,
	tsysAssetTypes.AssetTypename,
	tsysAssetTypes.AssetTypeIcon10 As icon,
	tblAssets.IPAddress,
	tblAssets.Lastseen,
	tblAssets.Lasttried,
	tblSoftwareUni.softwareName,
	--tblFileVersions.FilePathfull, report has been changed to not show found paths
	--was likely a source of you seeing duplicates
	Case
		When tblFileVersions.Found Is Null Then 'Not checked'
		Else 'Not found'
		--changed name
	End As FilePresence
From tblAssets
	Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
	Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
	Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
	--Changed to Left Join
	Left Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Where 
	tblSoftwareUni.softwareName Like 'GSG Windows Application'
	--might be good to remove the below line as well, not sure 
	And	tblFileVersions.FilePathfull Like '%certadddate.txt%' 
	--And (tblFileVersions.Found Is Null Or tblFileVersions.Found = 0)
	--This should make it so the report will not show assets that have the cert file 
	--in at least one location
	And tblAssets.AssetID not in (
		Select tblFileVersions.AssetID
		From tblFileVersions
		Where
			tblFileVersions.FilePathfull Like '%certadddate.txt%'
	)
	And	tblFileVersions.Found = 1
Order By tblAssets.AssetName