Report containing asset user relation + asset to asset relation

[ArjanvA]

Good afternoon,

I'm trying to build a report on a certain hardware we have. What i'm tring to achieve is that we have an overview of all of these devices and the Simcard theyre holding. (filtered on manufacturer and model)
The overview must include some hardware specifics of the parent asset as well as some specifics of the child asset (asset to asset relation) as well as the current owner (asset to user relation).
I've attempted to create this but fail to get hardware that does not have a asset to asset relation or asset to user relation. It must have both to come up in the results. I've been trying to wrap my head around the joins but cannot get it to work. Does anyone have experience with this and able to point me in the right direction?

What I have so far:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber As scanner_serial,
  tblAssetCustom.Custom7 As scanner_imei,
  tblAssetCustom.PurchaseDate As scanner_purchase_date,
  tblAssetCustom.Warrantydate As scanner_warranty_date,
  tsysAssetRelationTypes.RelationTypeIcon10 As Icon3,
  tsysAssetRelationTypes.ReverseName As RelationType,
  ChildAsset.AssetName As hyperlink_name_ChildAsset,
  tblAssetRelations.Comments As RelationComments,
  tblAssetRelations.StartDate As RelationStartDate,
  tblAssetRelations.EndDate As RelationEndDate,
  ChildAsset.AssetTypeIcon10 As Icon2,
  '/asset.aspx?AssetID=' + Cast(ChildAsset.AssetID As nvarchar(10)) As
  hyperlink_ChildAsset,
  '/Report/report.aspx?det=Web50getdomain&title=Computers in domain ' +
  ChildAsset.Domain + '&@domain=' + ChildAsset.Domain As
  hyperlink_ChildAssetDomain,
  ChildAsset.AssetTypename As ChildAssetType,
  Case
    When tblAssetRelations.EndDate < GetDate() Then '#dddddd'
    Else '#ffffff'
  End As backgroundcolor,
  tblADusers.Displayname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblAssetRelations On
    tblAssets.AssetID = tblAssetRelations.ParentAssetID
  Inner Join tsysAssetRelationTypes On tblAssetRelations.Type =
    tsysAssetRelationTypes.RelationTypeID
  Inner Join (Select Top 1000000 tblAssets.AssetID,
        tblAssets.AssetName,
        tsysAssetTypes.AssetTypeIcon10,
        tblAssets.Domain,
        tsysAssetTypes.AssetTypename,
        tblAssets.IPAddress
      From tblAssets
        Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
        Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
          tblAssets.Assettype
      Where tblAssetCustom.State = 1) As ChildAsset On
    tblAssetRelations.ChildAssetID = ChildAsset.AssetID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Inner Join tblAssetUserRelations On
    tblAssets.AssetID = tblAssetUserRelations.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
Where tblAssetCustom.Manufacturer = 'Zebra' And tblAssetCustom.Model = 'TC57HO'
  And tblState.Statename = 'Active'
Order By Case
    When tblAssetRelations.EndDate Is Null Then 1
    Else 0
  End Desc,
  RelationEndDate Desc,
  RelationStartDate Desc,
  tblAssets.AssetName

[Bruce_B]

If you want results that have just an asset<->asset relation or just an asset<->user relation you'll need to change your joins of the asset/user relation tables to left joins. You may need to modify your where clause afterwards though if you get too many results.