→ 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: 
crashff
Champion Sweeper
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]
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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]

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
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]