Asset assigned to User

[crashff]

I have the following query for finding Monitors 'used by' users, but I ran into one hiccup. I also need to be able to see the monitors that don't have the 'used by' relation set as well.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Convert(nvarchar(10),tblAssetCustom.PurchaseDate,120) As [Purchase Date],
tblAssetCustom.Custom3 As Qty,
Convert(integer,tblAssetCustom.Custom1) As [Purchase Price],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
Convert(nvarchar(10),tblAssetCustom.Lastchanged,120) As [Date Saved],
tblAssetUserRelations.Username
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
Where tsysAssetTypes.AssetType = 208
Order By [Purchase Date],
[Purchase Price]

[Daniel_B · Accepted solution]

Change the join to tblAssetUserRelations to a Left Join. Optionally add a function as we did below to write 'not assigned' if there exists no data in the joined table.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Convert(nvarchar(10),tblAssetCustom.PurchaseDate,120) As [Purchase Date],
  tblAssetCustom.Custom3 As Qty,
  Convert(integer,tblAssetCustom.Custom1) As [Purchase Price],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  Convert(nvarchar(10),tblAssetCustom.Lastchanged,120) As [Date Saved],
  Coalesce(tblAssetUserRelations.Username, 'not assigned') As [User]
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
Where tsysAssetTypes.AssetType = 208
Order By [Purchase Date],
  [Purchase Price]