Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblassets.Lasttried,
  Case
    When Software1.AssetID Is Not Null Then 'Yes'
    Else 'No'
  End As 'Software1',
  Case
    When Software2.AssetID Is Not Null Then 'Yes'
    Else 'No'
  End As 'Software2',
  Case
    When Software3.AssetID Is Not Null Then 'Yes'
    Else 'No'
  End As 'Software3'
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Left Join (Select tblSoftware.softwareVersion,
      tblSoftwareUni.softwareName,
      tblSoftware.AssetID
    From lansweeperdb.dbo.tblSoftware
      Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Software1%') Software1 On
      Software1.AssetID = tblassets.AssetID
  Left Join (Select tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion,
      tblSoftware.AssetID
    From lansweeperdb.dbo.tblSoftware
      Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Software3%') Software3 On
      Software3.AssetID = tblassets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From lansweeperdb.dbo.tblSoftware
      Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Software2%') Software2 On
      Software2.AssetID = tblassets.AssetID
  Inner Join lansweeperdb.dbo.tblComputersystem On tblassets.AssetID =
      tblComputersystem.AssetID
Where tsysassettypes.AssetTypename Like 'Windows' And
  tblComputersystem.Domainrole > 1 And tblassetcustom.State = 1