Trying to show display name along with software and computer name

[adamentally]

When I run this, the first name, last name, and display name columns come out blank. I know they're not blank. I'm surprised I can't link tblADusers to tblAssets by username. Anyone know why? Thanks in advance!


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblSoftwareUni.softwareName,
tblAssetCustom.State,
tblADusers.Firstname,
tblADusers.Lastname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblADObjects On tblADObjects.ADObjectID = tblADComputers.ADObjectID
And tblADObjects.ADObjectID = tblADComputers.ManagerADObjectId
Left Join tblADusers On tblADObjects.ADObjectID = tblADusers.ADObjectID And
tblADObjects.ADObjectID = tblADusers.ManagerADObjectId
Where tblSoftwareUni.softwareName Like 'SOLIDWORKS 2017 SP04.1' And
tblAssetCustom.State = 1

[adamentally · Accepted solution]

Hey HockeyNut,

Thanks for your reply, but I was able to figure it out. Here's what I ended up running:



Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblSoftwareUni.softwareName,
tblAssetCustom.State,
tblADusers.Displayname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblSoftwareUni.softwareName Like 'SOLIDWORKS 2017 SP04.1' And
tblAssetCustom.State = 1




Turns out you can join the ADusers table on the tblAssets table by username, but it's not given as an automatic option when you click the "Link" button. Then I was able to get the display name from ADUsers.

[AZHockeyNut]

since I do not have that software I get no results. I get plenty if I remove your where clause.

my guess is that you are looking for an exact match when you have 'SOLIDWORKS 2017 SP04.1' so, start by removing the clause, and see if you get results like I do.
Assuming you do, then the problem is in that clause, it is too limiting.
try '%SOLIDWORKS 2017 SP04.1%' or '%SOLIDWORKS 2017%'

run a query on tblSoftwareUni.softwareName
something like select * from tblSoftwareUni.softwareName where tblSoftwareUni.softwareName like '%Solidworks% and see what you get, from there you can begin to narrow it down.

if it is case sensitive for some reason you can use UCASE() to put softwarename in uppercase like your SOLIDWORK and then even a lower or mixed case version should show.

hope that helps