Query returning results that it should not - No DBAs on staff :(

[jackrock]

Hello. I need help with a SQL query I'm trying to run in LS. 

End goal: we want to find all machines with Java installed, but NOT include anything that has the SE Development Kit OR a software known as "Ignition".  [Has: Java but NOT (SE Development Kit OR Ignition)]

Here's the query as it stands: 

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname As OS,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
'black' As foregroundcolor,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Java%' And
Not (tblSoftwareUni.softwareName Like '%SE Development Kit%' Or
tblSoftwareUni.softwareName Like 'Ignition%')) And tblAssetCustom.State =
1 And tblComputersystem.domainrole = 1
Order By tblAssets.Domain,
tblAssets.AssetName

TIA

[jackrock]

Sadly, that did not work. I'm still getting results that have Ignition installed. It seems that it's sufficiently filtering out the Development kit, but not Ignition (which is exactly what was going on before). 

[KevinA-REJIS]

I added an additional wildcard in the code before Ignition, see if that makes a difference (unforunately we don't use it so I can't test that part).

[jackrock]

No dice. We're currently researching some option where we do multiple WHERE statements and basically filter out Results B from Results A. 

Bleh. I wish I'd taken a SQL class in a former life.

[KevinA-REJIS]

See if this code will work for you (if an asset has multiple Java versions, it will show up multiple times):

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname As OS,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  'black' As foregroundcolor,
  tblComputersystem.Domainrole
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.SoftID
Where tblSoftwareUni.softwareName Like 'Java%' And
  tblSoftwareUni.softwareName Not Like '%SE Development Kit%' And
  tblSoftwareUni.softwareName Not Like '%Ignition%' And tblAssetCustom.State = 1
  And tblComputersystem.domainrole = 1
Order By tblAssets.Domain,
  tblAssets.AssetName