→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
teddyh2o
Engaged Sweeper
I am trying to get a full report that shows assets with and without ad relationship.
My query currently just shows the assets that have a relation to tblAssetUserRelations.
If there isn't any relationship, i want show NULL if there isn't any.

here is my query.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tblAssetUserRelations.Username,
tblAssets.Scanserver
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Assettype = -1
Order By tblAssets.AssetName
1 REPLY 1
Andy_Sismey
Champion Sweeper III
Hi,

Is this what your after :

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Case
When tblAssetUserRelations.Username Is Null Then 'No'
Else 'Yes'
End As Relationship,
tsysAssetTypes.AssetTypename As AssetType,
tblAssetUserRelations.Username,
tblAssets.Scanserver
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Assettype = -1
Order By tblAssets.AssetName