Need a bit of help

[David_Elliott]

SQL query noob here.

I'm looking for the existence of two files. With this code, I receive a line item if a machine is false for either of the files. I receive two line items if the machine is false for both files. I would like to ignore the workstations with a single line item and only report on machines missing both files but do that in a single line.


Select Top 1000000 IsNull(dbo.tblComputerSystemProduct.Name, 'Unknown') As Model, dbo.tblComputers.Username, dbo.tblComputers.Computer, tblADComputers.OU, dbo.tblComputers.Lastseen, tblFileVersions.Found, tblFileVersions.FilePathfull, dbo.tblComputers.Computername From dbo.tblComputers Inner Join dbo.tblComputerSystemProduct On dbo.tblComputers.Computername = dbo.tblComputerSystemProduct.Computername Inner Join dbo.web40ActiveComputers On dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername Inner Join tblADComputers On dbo.tblComputers.Computername = tblADComputers.Computername Inner Join tblFileVersions On dbo.tblComputers.Computername = tblFileVersions.Computername Where IsNull(dbo.tblComputerSystemProduct.Name, 'Unknown') Like '%optiplex%' And tblFileVersions.Found = 'false' And tblFileVersions.FilePathfull Like '%safeboot.sys' And dbo.tblComputers.LastknownIP Like '%10.%' Or IsNull(dbo.tblComputerSystemProduct.Name, 'Unknown') Like '%optiplex%' And tblFileVersions.Found = 'false' And tblFileVersions.FilePathfull Like '%epepcmonitor.exe' And dbo.tblComputers.LastknownIP Like '%10.%' Order By dbo.tblComputers.Username

[David_Elliott]

that helped. thanks

[Hemoco · Accepted solution]

Please use the report below for the information you are after.

Select Top 1000000 IsNull(tblComputerSystemProduct.Name, 'Unknown') As Model, Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Username, tblComputers.Computer, tblADComputers.OU, tblComputers.Lastseen From tblComputers Inner Join tblFileVersions On tblComputers.Computername = tblFileVersions.Computername Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblADComputers On tblComputers.Computername = tblADComputers.Computername Inner Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Where IsNull(tblComputerSystemProduct.Name, 'Unknown') Like '%optiplex%' And tblComputers.Computername In (Select tblComputers.Computername From tblComputers Inner Join tblFileVersions On tblComputers.Computername = tblFileVersions.Computername Where tblFileVersions.Found = 'false' And tblFileVersions.FilePathfull Like '%safeboot.sys') And tblFileVersions.Found = 'false' And tblFileVersions.FilePathfull Like '%epepcmonitor.exe' And tblComputers.LastknownIP Like '%10.%' Order By tblComputers.Username