Program Versions (List servers just once on one line not 2)

[TRC_System_Admi]

Hello,

I know how to search programs an features to echo back a version number of an installed app..

How can I search for 2 Installed programs on 1 report? I know I can use 'OR' but that will list the same server twice on different lines.

Basically how can I write the code below so everything stays on 1 line?

Right Way
Server1 - Software1 - Software2

Wrong Way
Server1 - Software1
Server1 - Software2

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress,
tblAssets.Username,
tsysOS.OSname,
tblSoftwareUni.softwareName As software,
tsysOS.Image As icon,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblSoftwareUni.softwareName Like 'Software1%'
Order By tblAssets.AssetUnique

Where tblSoftwareUni.softwareName Like 'Software2%'
Order By tblAssets.AssetUnique

I know this must be possible because I can do this when using 2 reg keys.

Thanks

[Daniel_B · Accepted solution]

For this you need to use subqueries in your report which only pull data about one software each. Please find an example below:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.IPAddress,
  tblAssets.Username,
  tsysOS.OSname,
  tSoftware1.softwareName As [software 1],
  tsysOS.Image As icon,
  tSoftware1.softwareVersion As [software 1 version],
  tSoftware2.softwareName As [software 2],
  tSoftware2.softwareVersion As [software 2 version]
From tblAssets
  Left Join (Select tblSoftware.AssetID,
    tblSoftwareUni.softwareName,
    tblSoftware.softwareVersion
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like 'software1%') tSoftware1
    On tSoftware1.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID,
    tblSoftwareUni.softwareName,
    tblSoftware.softwareVersion
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like 'software2%') tSoftware2
    On tSoftware2.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Assettype = -1
Order By tblAssets.AssetUnique