Monitors by relation including tblMonitor data

[esr]

There are days I can't SQL my way out of a paper bag. Today appears to be one of those....

Quick & basic monitor report also finds the build in screens on Laptops, which I don't want. So, I've got a report using AssetRelations & AssetRelationTypes that works perfectly- the counts are exactly right.

I'd like to add data from tblMonitor as well so I can include MonitorModel and ManufactureDate.

I'm linking tblMonitor from tblAssets1, which is tied to the relation data, matching tblAssets1.AssetID to tblMonitor.MonitorID- Again, the counts are right, but only a portion of the tblMonitor data appears. I get the same results in SMS. I think it's just a join issue, but was unable to resolve it myself.

The query below includes the tblMonitor items and screenshot of results-

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tsysAssetRelationTypes.Name As Relation,
  tblAssets1.AssetName As Monitor,
  tblMonitor.ManufacturedDate,
  tblMonitor.MonitorModel
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetRelations On tblAssetRelations.ParentAssetID =
    tblAssets.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetRelations.Type
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
    tblAssetRelations.ChildAssetID
  Inner Join tsysAssetTypes tsysAssetTypes1 On tsysAssetTypes1.AssetType =
    tblAssets1.Assettype
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblMonitor On tblMonitor.MonitorID = tblAssets1.AssetID
Where tsysAssetRelationTypes.Name = 'connected to' And
  tsysAssetTypes1.AssetTypename = 'monitor' And tblADComputers.OU Like '%HQN%'
Order By tblAssets.AssetName

[Susan_A · Accepted solution]

The AssetID of the monitor asset in tblAssets doesn't actually correspond with the MonitorID in tblMonitor, which is why you're getting inaccurate results. You can try something like this to link the tables instead:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tsysAssetRelationTypes.Name As Relation,
  tblAssets1.AssetName As Monitor,
  tblMonitor.ManufacturedDate,
  tblMonitor.MonitorModel
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetRelations On tblAssetRelations.ParentAssetID =
    tblAssets.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetRelations.Type
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
    tblAssetRelations.ChildAssetID
  Inner Join tsysAssetTypes tsysAssetTypes1 On tsysAssetTypes1.AssetType =
    tblAssets1.Assettype
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblMonitor On 'mon:' + tblMonitor.MonitorManufacturer + ':' +
    tblMonitor.SerialNumber = tblAssets1.AssetUnique
Where tsysAssetRelationTypes.Name = 'connected to' And
  tsysAssetTypes1.AssetTypename = 'monitor' And tblADComputers.OU Like '%HQN%'
Order By tblAssets.AssetName