Report showing multiple entries for same device

[jatinder]

I am making a custom report that works but entries are duplicated multiple times. I have 78 printers and the report displays the printers IP location, Manufacturer, Model, SerialNumber, AssetName, Location (Custom Field 01), and the IPAddress.

I have 78 printers but it shows 869 entries (each printer is repeated 11 times). What am I doing wrong - any direction would be helpful.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssets.AssetName,
tblAssetCustom.Custom1 As Location,
tblAssetCustom.AssetID,
tblAssets.IPAddress
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID,
tblAssetGroups
Where tblAssetCustom.State = 1

thank you,

Jatinder

[RCorbeil · Accepted solution]

You're including tblAssetGroups but you're not JOINing it to anything else in your query, so you're seeing the results repeated for every row in tblAssetGroups.

If you check the database documentation, you'll find that if you want to link against tblAssetGroups, you need to go through tblAssetGroupLink.

INNER JOIN tblAssetGroupLink ON tblAssetGroupLink.AssetID = tblAssets.AssetID
INNER JOIN tblAssetGroups ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID

Note that if you do that, you still stand a chance of seeing repeated results for a given asset if that asset is assigned to multiple groups.

If all you're wanting is your printers, you can drop tblAssetGroups from your query entirely and filter on tsysAssetTypes.AssetTypename = 'Printer'.

[jatinder]

Was looking for Printers only, so solution:
If all you're wanting is your printers, you can drop tblAssetGroups from your query entirely and filter on tsysAssetTypes.AssetTypename = 'Printer'

thank you,