Report for Multiple Files Existing on Desktop

[Anonymous]

I have 2 versions of a report that I want to get to work to accurately show scanned information. I am not sure if I have the correct joining of tables or if something else is wrong. 

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADusers.Username,
tblADusers.Firstname,
tblADusers.Lastname,
Case
When Gazette.FilePathfull Is Not Null Then 'YES'
Else 'NO'
End As Gazette,
Case
When ServiceDesk.FilePathfull Is Not Null Then 'YES'
Else 'NO'
End As [Service Desk Portal],
Case
When MIDMail.FilePathfull Is Not Null Then 'YES'
Else 'NO'
End As [MID Mail],
Case
When OfficeApps.FilePathfull Is Not Null Then 'YES'
Else 'NO'
End As [OFFICE APPS],
Gazette.Filesize As Gazette_Filesize,
ServiceDesk.Filesize As ServiceDesk_Filesize,
MIDMail.Filesize As MIDMail_Filesize,
OfficeApps.Filesize As OfficeApps_Filesize,
Gazette.Lastchanged As Gazette_Lastchanged,
Gazette.LastModified As Gazette_LastModified,
ServiceDesk.Lastchanged As ServiceDesk_Lastchanged,
ServiceDesk.LastModified As ServiceDesk_LastModified,
MIDMail.Lastchanged As MIDMail_Lastchanged,
MIDMail.LastModified As MIDMail_LastModified,
OfficeApps.Lastchanged As OfficeApps_Lastchanged,
OfficeApps.LastModified As OfficeApps_LastModified
From tblAssets
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Left Join tblFileVersions As Gazette On tblAssets.AssetID = Gazette.AssetID
And Gazette.FilePathfull = 'C:\Users\Public\Desktop\Gazette.url'
Left Join tblFileVersions As ServiceDesk On tblAssets.AssetID =
ServiceDesk.AssetID And ServiceDesk.FilePathfull =
'C:\Users\Public\Desktop\Service Desk Portal.url'
Left Join tblFileVersions As MIDMail On tblAssets.AssetID = MIDMail.AssetID
And MIDMail.FilePathfull = 'C:\Users\Public\Desktop\MID Mail.url'
Left Join tblFileVersions As OfficeApps On tblAssets.AssetID =
OfficeApps.AssetID And OfficeApps.FilePathfull =
'C:\Users\Public\Desktop\OFFICE APPS.url'
Order By tblAssets.AssetName

Currently it does not appear to be accurate as I know some of the workstations do not have the shortcut (even after running a scan), but the report says it does.

------------------------------------------

I also tried this version but it also gives inaccurate information: 

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADusers.Username,
tblADusers.Firstname,
tblADusers.Lastname,
Case
When Exists(Select 1 From tblFileVersions
Where tblAssets.AssetID = tblFileVersions.AssetID And
tblFileVersions.FilePathfull = 'C:\Users\Public\Desktop\TLM.url') Then
'YES'
Else 'NO'
End As TLM_Exists
From tblAssets
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Order By tblAssets.AssetName

------------------------------------------

This is a report I was working with before that accurately only shows the workstations that have the file:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADusers.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblFileVersions.Found As TLM,
tblFileVersions.FilePathfull,
tblFileVersions.Filesize,
tblFileVersions.Lastchanged,
tblFileVersions.LastModified,
tblFileVersions.CreationDate,
tblFileVersions.LastAccessed
From tblAssets
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username,
tblState
Where tblFileVersions.Found = 'True' And tblFileVersions.FilePathfull =
'C:\Users\Public\Desktop\TLM.url' And tblState.Statename = 'Active'
Order By tblAssets.AssetName

Why does the last report work for it's purpose, but the first 2 reports do not accurately show a No or a Yes? 

My goal is to have a report showing whether multiple/specific shortcuts exist on the user's profiles so we can update them as needed

[rom]

Here's what I do when doing multiple checks within a single report:

Left Join (Select tblFileVersions.AssetID,
      tblFileVersions.Found,
      tblFileVersions.FilePathfull,
      tblFileVersions.FileVersion,
      tblFileVersions.CompanyName,
      tblFileVersions.Filesize,
      tblFileVersions.Lastchanged
    From tblFileVersions
    Where tblFileVersions.FilePathfull Like '%ccmsetup.exe%') hassccm On
      tblAssets.AssetID = hassccm.AssetID

You then:

Case
    When hassccm.Found = 1 Then 'Yes'
    Else 'No'
  End As SCCM

Here's a windows service check:

Left Join (Select Top 1000000 tblServices.AssetID
    From tblServices
      Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
          tblServices.ServiceuniqueID
    Where tblServicesUni.Name = 'cyserver') As checkforxdr On
      tblAssets.AssetID = checkforxdr.AssetID

[Mister_Nobody]

Try to use UNION with two  accurately report with different files

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=...

[Anonymous]

Which part would I union?  I can't say I have ever used a union before. 
I tried understanding from the link you gave me, but perhaps I need to watch a video.