Full asset report with and without ad relationship

[teddyh2o]

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

[Andy_Sismey]

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