cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tholmes
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

2 REPLIES 2
Tholmes
Engaged Sweeper III
Thank you so much, hoping this can help towards me, hopefully, not asking anymore questions about these queries.
Tim
RCorbeil
Honored Sweeper II
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