Assets only in Default Group not showing

[PeterAJ]

Hi,

I am trying (perhaps in a clumsy way!) to make a report of assets with one or more missing custom fields filled out. It sort of works, but my problem is, that assets only in Default Group doesn't show up! If I then assign the assets to another group, it shows up, even if I remove it from the group again!

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tblAssetGroups.AssetGroup,
tblassetcustom.Custom8 As System,
tblassetcustom.Custom1 As Service,
tblassetcustom.PurchaseDate,
tblassetcustom.Custom3 As HW,
tblassetcustom.Custom2 As VLAN,
tblassetcustom.Custom4 As Building,
tblassetcustom.Custom7 As Priority,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.Firstseen
From tblassets
Inner Join tblAssetGroupLink On tblassets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Where (tblassets.AssetName <> 'None' And tblassetcustom.Custom8 <> 'NoSystem'
And tblassetcustom.Custom2 = '' And tblassetcustom.State = 1) Or
(tblassets.AssetName <> 'None' And tblassetcustom.Custom8 <> 'NoSystem' And
tblassetcustom.Custom1 = '' And tblassetcustom.State = 1) Or
(tblassets.AssetName <> 'None' And tblassetcustom.Custom8 = '' And
tblassetcustom.Custom8 <> 'NoSystem' And tblassetcustom.State = 1) Or
(tblassets.AssetName <> 'None' And tblassetcustom.Custom8 <> 'NoSystem' And
tblassetcustom.Custom3 = 'yes' And tblassetcustom.Custom4 = '' And
tblassetcustom.State = 1) Or
(tblassets.AssetName <> 'None' And tblassetcustom.Custom8 <> 'NoSystem' And
tblassetcustom.PurchaseDate = '' And tblassetcustom.Custom3 = 'yes' And
tblassetcustom.State = 1)
Order By tblassets.Firstseen Desc

Any help appreciated 🙂

Thank you

Kind regards Peter

[rinks]

Try using a Left Join instead of Inner Join:

SELECT TOP 1000000
tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tblAssetGroups.AssetGroup,
tblassetcustom.Custom8 AS System,
tblassetcustom.Custom1 AS Service,
tblassetcustom.PurchaseDate,
tblassetcustom.Custom3 AS HW,
tblassetcustom.Custom2 AS VLAN,
tblassetcustom.Custom4 AS Building,
tblassetcustom.Custom7 AS Priority,
tsysassettypes.AssetTypeIcon10 AS icon,
tblassets.Firstseen
FROM tblassets
LEFT JOIN tblAssetGroupLink ON tblassets.AssetID = tblAssetGroupLink.AssetID
LEFT JOIN tblAssetGroups ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
INNER JOIN tblassetcustom ON tblassets.AssetID = tblassetcustom.AssetID
INNER JOIN tsysassettypes ON tsysassettypes.AssetType = tblassets.Assettype
WHERE tblassets.AssetName <> 'None'
AND tblassetcustom.State = 1
AND tblassetcustom.Custom8 <> 'NoSystem'
AND (
tblassetcustom.Custom2 = '' OR
tblassetcustom.Custom1 = '' OR
(tblassetcustom.Custom8 = '' AND tblassetcustom.Custom8 <> 'NoSystem') OR
(tblassetcustom.Custom3 = 'yes' AND tblassetcustom.Custom4 = '') OR
(tblassetcustom.Custom3 = 'yes' AND tblassetcustom.PurchaseDate = '')
)
ORDER BY tblassets.Firstseen DESC

By switching to Left Join, you ensure that all assets are included—even if they don't have a group or are only in the "Default Group."