Duplicate Entries

[RandyCosta]

I have put a report together which tells me all of my SQL servers.  It was working fine until I added tblProcessor to get Cores.  Now I have duplicate entries in my report.

Does anyone know a way which I can reduce all this to only show each PC once?

I'm using the following statement:

Select Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.NrProcessors As [# Of Processors],
tblProcessor.NumberOfCores,
tblProcessor.NumberOfLogicalProcessors,
tblAssets.Memory,
tblSqlServers.skuName As License,
tblSqlServers.displayVersion As Edition,
tsysOS.OSname As OS,
tblAssets.Username,
tblAssets.Userdomain,
tblSqlServers.serviceName As Service,
tsysIPLocations.IPLocation,
tblAssets.Description,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
Edition

[Tim_N]

Hello @RandyCosta 

I am not a SQL query guru... but I tested your query and was returned duplicate results as you mentioned. Then, I made a few changes and was returned a single result. 

My lab is very limited, so you'll want to test this, double-checking the results to ensure it's working as intended and returning the desired results. 

SELECT TOP 1000000
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) AS icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.NrProcessors AS [# Of Processors],
MAX(tblProcessor.NumberOfCores) AS NumberOfCores,
MAX(tblProcessor.NumberOfLogicalProcessors) AS NumberOfLogicalProcessors,
tblAssets.Memory,
tblSqlServers.skuName AS License,
tblSqlServers.displayVersion AS Edition,
tsysOS.OSname AS OS,
tblAssets.Username,
tblAssets.Userdomain,
tblSqlServers.serviceName AS Service,
tsysIPLocations.IPLocation,
tblAssets.Description,
tblAssets.Lastseen
FROM tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblSqlServers ON tblAssets.AssetID = tblSqlServers.AssetID
LEFT JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
LEFT JOIN tsysIPLocations ON tblAssets.LocationID = tsysIPLocations.LocationID
INNER JOIN tblProcessor ON tblAssets.AssetID = tblProcessor.AssetID
WHERE tblAssetCustom.State = 1
GROUP BY
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10),
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.NrProcessors,
tblAssets.Memory,
tblSqlServers.skuName,
tblSqlServers.displayVersion,
tsysOS.OSname,
tblAssets.Username,
tblAssets.Userdomain,
tblSqlServers.serviceName,
tsysIPLocations.IPLocation,
tblAssets.Description,
tblAssets.Lastseen
ORDER BY tblAssets.AssetName,
Edition;