Trying to display different versions of software

[Tholmes]

Here is my code so far, I copied it and thought I could just add to it to ask for columns at the top, software name and version

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%OneDrive' And
tblSoftware.softwareVersion Not Like '19.232%') And
tblAssets.Lastseen <> '' And tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

As always, any and all help greatly appreciated.
I am starting on basic SQL courses, the free ones seem good, any suggestions for report writing courses please?
Thanks in advance
Tim

[Tholmes]

Thank you so much, hoping this can help towards me, hopefully, not asking anymore questions about these queries.
Tim

[RCorbeil · Accepted solution]

To display fields from the software tables you need to select those tables. What you're currently doing isn't including the tables directly, it's just pulling a list of AssetID values that happen to be from tblSoftware.

By using the sub-SELECT, you're Dr. Frankenstein (the main SELECT) asking Igor (the sub-SELECT) to go check the software tables for machines that match conditions X and Y, then give you a list of the machines that matched. You don't get to see any details, just the final list of machine IDs. By including the software tables in your main query, you're doing the checking yourself and can thus reference all the associated details.

Try this:

Select Top 1000000
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress As IP,
  tblSoftwareUni.SoftwareName,
  tblSoftware.softwareVersion
From
  tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblSoftware ON tblSoftware.AssetID = tblAssets.AssetID
  INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
Where
  tblSoftwareUni.softwareName Like '%OneDrive'
  And tblSoftware.softwareVersion Not Like '19.232%'
  And tblAssets.Lastseen <> ''
  And tblAssets.Assettype = -1
Order By
   tblAssets.AssetUnique