Why tblMonitor is bound to assets of type -1 and not 208

[SandroR]

I wonder why the foreign key AssetId of tblMonitor is bound to the WindowsPC and not to the Asset of type monitor.

I need to retrieve those columns, but due the relation of the record I obtain a lot of duplicated rows

Try before and after un-commenting the tblmonitor fields...

Select Top (1000000) 
pc.AssetID AS AssetPcId,
pc.AssetName AS AssetPcName,
monitor.AssetID AS AssetMonitorId,
monitor.AssetName AS AssetMonitorName,
monitor.Description AS AssetMonitorDescription,
monitorcustom.Custom1 AS FixedAssetNo,
monitorcustom.Serialnumber AS SerialNumberFromCustom
--,moninfo.SerialNumber,
--moninfo.MonitorManufacturer,
--moninfo.MonitorModel,
--moninfo.ManufacturedDate
from tblAssets monitor
INNER JOIN dbo.tblAssetRelations asr ON monitor.AssetID = asr.ChildAssetID 
INNER JOIN dbo.tblAssets pc ON pc.AssetID = asr.ParentAssetID AND pc.Assettype = -1
INNER JOIN dbo.tblAssetCustom monitorcustom ON monitorcustom.AssetID = monitor.AssetID
--INNER JOIN dbo.tblMonitor monInfo ON monInfo.AssetID = pc.AssetID
WHERE monitor.Assettype = 208 
ORDER BY monitor.Description

What am I missing?

Thanks in advance.

[Mister_Nobody]

Addition:

Select Top 1000000 tblMonitor.*,
  tblAssets.AssetUnique,
  tblAssets.OScode,
  (Select tsysManufacturerTranslation.manufacturer
    From tsysManufacturerTranslation
    Where Replace(Replace(tsysManufacturerTranslation.regex, '$', ''),
      '^', '') = tblMonitor.MonitorManufacturer) As manufacturer
From tblMonitor
  Inner Join tblAssets On tblAssets.AssetID = tblMonitor.AssetID

[SandroR]

Good to know where the fullname of Manufacturer is... 

COALESCE(
(Select tsysManufacturerTranslation.manufacturer
From tsysManufacturerTranslation
Where Replace(Replace(tsysManufacturerTranslation.regex, '$', ''),
'^', '') = tblMonitor.MonitorManufacturer)
,tblMonitor.MonitorManufacturer)

[Mister_Nobody]

I don't use tblAssetRelations.

You can use direct inventory info:

Select Top 1000000 tblMonitor.*,
  tblAssets.AssetUnique,
  tblAssets.OScode
From tblMonitor
  Inner Join tblAssets On tblAssets.AssetID = tblMonitor.AssetID

[SandroR]

Unfortunately doesn't work because I need a Custom1 field that is written in the Asset of Type 208 

so for example...
PC Asset => AssetId = 100
Monitor Asset => AssetId = 200
AssetRelation ParentChild 100-200
tblAssetCustom related to AssetId=200
tblMonitor related to AssetId = 100 (why!!!)

this cause the join to have more rows repeated and with distinct you can't resolve the issue (maybe is my limited sql skill)

[Mister_Nobody]

We have a lot of monitors which don't present as asset:

Select Top 1000000 tblMonitor.MonitorModel As MonitorModel1,
  tblMonitor.SerialNumber As SerialNumber1,
  tblMonitor.MonitorManufacturer As MonitorManufacturer1,
  tblMonitor.ManufacturedDate As ManufacturedDate1,
  tblMonitor.LastChanged As LastChanged1,
  tblMonitor.PNPDeviceID As PNPDeviceID1,
  tblAssets.AssetUnique,
  tblAssets.OScode,
  tblAssets.AssetID,
  tblAssets.LastLsPush,
  tblAssets.LsPushVersion,
  tblAssets.LastActiveScan,
  tblAssets.Lastseen,
  tblAssets.Username,
  tblAssets.IPAddress
From tblMonitor
  Inner Join tblAssets On tblAssets.AssetID = tblMonitor.AssetID
Where tblMonitor.AssetID Not In (Select tblAssetRelations.ParentAssetID
    From tblAssetRelations)

[SandroR]

We too and that would be a secondary problem...  So summarizing my needs:

I want have a list of All Monitors (type=208) with a column taken of Custom1 (tblAssetCustom), with their associated PC (NULL if doesn't exists the relation) and with the manufacturer name and model (tblMonitor)

[Mister_Nobody · Accepted solution]

OK!
ALL scanned monitors with manufacturer and model - passed
Custom columns - passed
All associated PC - passed

Select Top 1000000 tblAssets.Assetid,
  tblAssets.Assetname,
  Coalesce((Select tsysManufacturerTranslation.manufacturer
    From tsysManufacturerTranslation
    Where Replace(Replace(tsysManufacturerTranslation.regex, '$', ''),
      '^', '') = tblMonitor.MonitorManufacturer),
  tblMonitor.MonitorManufacturer) As Manufacturer,
  tblMonitor.MonitorModel,
  tblMonitor.SerialNumber,
  tblMonitor.ManufacturedDate,
  tblAssetCustom.Custom1 As [Inventory N],
  tblAssetCustom.Custom2 As Location,
  tblAssetCustom.Custom3 As Owner,
  tblAssetCustom.Custom4 As [Issue Date]
From tblMonitor
  Inner Join tblAssets On tblAssets.AssetID = tblMonitor.AssetID
  Left Join tblAssetCustom On tblMonitor.SerialNumber =
      tblAssetCustom.Serialnumber And tblMonitor.MonitorModel =
      tblAssetCustom.Model

[SandroR]

WOW!!! smart move to join not thru Foreign Key but other fields...

I have only to check why SELECT * FROM tblAssets WHERE Assettype = 208 returns me 7 rows more, but I think there are old records where the scan doesn't return model or s/n

THANKS A LOT!

[Mister_Nobody]

You can remove such assets or change asset type via mass edit.