List all hostnames that have installed three programs with specific versions but not Software 4

[Starbase12]

Hey together,

after my first question is already marked as solved I'm having another question:

How I have to adjust this report to list computers, which are having Software 1, Software 2 and Software 3 installed but NOT Software 4? Whatevery I tried it didn't worked (sometimes every PCs was listed multiple times for each installed software).

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tsysOS.OSname As OS,
  tblassets.Version As Build,
  tblassets.Username,
  s1.SoftwarePublisher As [soft1 Publisher],
  s1.softwareName As [soft1 Name],
  s1.softwareVersion As [soft1 Version],
  s2.SoftwarePublisher As [soft2 Publisher],
  s2.softwareName As [soft2 Name],
  s2.softwareVersion As [soft2 Version],
  s3.SoftwarePublisher As [soft3 Publisher],
  s3.softwareName As [soft3 Name],
  s3.softwareVersion As [soft3 Version],
  s4.SoftwarePublisher As [soft4 Publisher],
  s4.softwareName As [soft4 Name],
  s4.softwareVersion As [soft4 Version],
  tblassets.Lastseen,
  tblassets.Lasttried
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%SAP Interactive Excel%') As s1 On
      s1.AssetID = tblassets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%SAP Business Explorer%') As s2 On
      s2.AssetID = tblassets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where
      tblSoftwareUni.softwareName Like '%SAP GUI for Windows 7.60  (Patch 5)%')
  As s3 On s3.AssetID = tblassets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%SAP Business Client 7.0%') As s4 On
      s4.AssetID = tblassets.AssetID
  Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblassets.OScode
Where s1.softwareName = 'SAP Interactive Excel' And s1.softwareVersion =
  '3.0.8.20036' And s2.softwareName = 'SAP Business Explorer' And
  s2.softwareVersion = '7.60' And
  s3.softwareName = 'SAP GUI for Windows 7.60  (Patch 5)' And
  s3.softwareVersion = '7.60 Compilation 1' And s4.softwareName =
  'SAP Business Client 7.0' And s4.softwareVersion = '7.0 PL10' And
  tblassetcustom.State = 1
Order By tblassets.AssetName

Thanks in advanced.

Original post: https://www.lansweeper.com/forum/yaf_postst20622_How-to-scan-where-multiple-software-with-specific-version-is-installed.aspx#post66033

[RCorbeil]

Answered where you added the question to your original.