Showing PC's without SSD only once

[andrewhancock91]

Hi all,

First post here, I've been viewing the forum for awhile while getting all my custom reports set up (no experience with SQL) and I've been able to figure everything out until now! Trying to generate a report that shows all PC's without an SSD, but only showing the asset ID once instead of multiple times for each Partition or drive letter on the system. I figure only showing C: drive should do the trick but its still showing everything else. Here's what I've came up with, sorry if its rough or incorrect!

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Memory,
tblAssets.Processor,
tblAssets.Username,
tblDiskdrives.FileSystem,
tblDiskdrives.Freespace,
tblDiskdrives.Size,
tblDiskdrives.Description,
tblDiskdrives.Caption
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where (tblDiskdrives.Caption = 'C:' And tblFloppy.Model Not Like 'OCZ%') Or
(tblFloppy.Model Not Like 'Kingston S%') Or
(tblFloppy.Model Not Like '%Solid State%') Or
(tblFloppy.Model Not Like '%SSD%') Or
(tblFloppy.Model Not Like '%NVMe%') Or
(tblFloppy.Model Not Like '%LITEON%') Or
(tblFloppy.Model Not Like '%MTFDDAV%') Or
(tblFloppy.Model Not Like '%Sandis%') Or
(tblFloppy.Model Not Like '%MZ-5S71000%') Or
(tblFloppy.Model Not Like '%MZ%')

Thanks!