Report to show assets in one group but not another.

[PeterJG]

I have assets that share multiple groups, I would like to create reports showing assets that are in GROUP1 but not in GROUP2. Tried different things but I'm not SQL guru just yet.. any help would be greatly appreciated.

-Thank YOu

[PeterJG]

Thank You.. exactly what I wanted..

[Daniel_B · Accepted solution]

For this you'll need to add subqueries to your report's SQL query which searches for assets in groups. Asset groups are stored in tblAssetgroups and the membership of assets in groups is stored in tblAssetGroupLink.
Here is an example report:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where 
  tblAssets.AssetID In (Select tblAssetGroupLink.AssetID
  From tblAssetGroupLink Inner Join tblAssetGroups
      On tblAssetGroupLink.AssetGroupID = tblAssetGroups.AssetGroupID
  Where tblAssetGroups.AssetGroup Like 'GROUP1') 
  And
  tblAssets.AssetID Not In (Select tblAssetGroupLink.AssetID
  From tblAssetGroupLink Inner Join tblAssetGroups
      On tblAssetGroupLink.AssetGroupID = tblAssetGroups.AssetGroupID
  Where tblAssetGroups.AssetGroup Like 'GROUP2') 
  And tblAssetCustom.State = 1