Duplicated results in report

[paultech]

Hi all,

I have put together a quick query to find the DNS server search order, which works ok generally speaking however each PC in the results has 9 additional duplicates

I was wondering if anyone knows how I can remove those duplicate rows with no values in
My report looks like this:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblOperatingsystem.Caption,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Username,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNetwork.DNSServerSearchOrder,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblAssetCustom.State = 1
Order By tblADComputers.OU

[RCorbeil · Accepted solution]

First thought: if you're specifically interested in the DNS search order, filter out any network interfaces that don't have any. Add to the WHERE clause

  AND tblNetwork.DNSServerSearchOrder <> ''

If you're still seeing too many interfaces, add more details from tblNetwork to your report to see if there's anything else you can filter on.

[paultech]

Thank you very much RC62N, that has worked perfectly