Unexpected results on where statement

[Jeff_Henze]

I'm a bit baffled by the fact that even though I specifically say I do not want to see records with a tblAssetCustom.Custom2 field like %retired%, I still get items with "Retired" in that field.

Here's the report:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Custom3 As Company,
  tblAssetCustom.Custom2 As Status,
  tblAssets.Lastseen,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblState.Statename,
  tblADComputers.Location,
  tblAssets.Description,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssetCustom.Manufacturer,
  tblADComputers.OU,
  tblComputersystem.Domainrole
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where (tblAssetCustom.Custom3 Not Like '%companyx%' Or
    tblAssetCustom.Custom3 Is Null) And (tblAssetCustom.Custom2 Not Like
    '%retired%' Or tblAssetCustom.Custom2 Not Like '%secondary%' Or
    tblAssetCustom.Custom2 Is Null) And tblAssetCustom.Model <>
  'Virtual Machine' And (tblAssetCustom.Manufacturer Like '%dell%' Or
    tblAssetCustom.Manufacturer Like '%microsoft%') And
  tblADComputers.OU Not Like '%denver%' And tblComputersystem.Domainrole < 2
Order By tblAssetCustom.PurchaseDate

That's not the only anomaly with my report, but I believe it's the most telling of what I'm doing wrong. I assume this is something obvious to a SQL-er, but I'm not seeing it.

Thanks,
-Jeff

[Jeff_Henze]

Thanks guys, that was indeed the problem with my query and the solution worked well. It was too many AND ORs for my brain, but incredibly obvious now that you spelled it out.

Thanks,
-Jeff

[Bruce_B · Accepted solution]

I've adjusted your reports to replace some OR's with AND's, if you want to your report to exclude multiple values, using OR will not work, as illustrated above. I wasn't sure if you also wanted to exclude NULL values, or if you wanted to see NULL values. The report below currently filters out NULL values, if you wish to see them, you can remove the highlighted portion of the code.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom2 As Status,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADComputers.Location,
tblAssets.Description,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblADComputers.OU,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Custom3 Not Like '%companyx%' And
tblAssetCustom.Custom3 Is Not Null And tblAssetCustom.Custom2 Not Like
'%retired%' And tblAssetCustom.Custom2 Not Like '%secondary%' And
tblAssetCustom.Custom2 Is Not Null And tblAssetCustom.Model <>
'Virtual Machine' And (tblAssetCustom.Manufacturer Like '%dell%' Or
tblAssetCustom.Manufacturer Like '%microsoft%') And
tblADComputers.OU Not Like '%denver%' And tblComputersystem.Domainrole < 2
Order By tblAssetCustom.PurchaseDate

[RCorbeil]

Looking at your WHERE clause

WHERE
      (    tblAssetCustom.Custom3 Not Like '%companyx%'
        OR tblAssetCustom.Custom3 Is Null
      )
  AND (    tblAssetCustom.Custom2 Not Like '%retired%'
        OR tblAssetCustom.Custom2 Not Like '%secondary%'
        OR tblAssetCustom.Custom2 Is Null
      )
  AND (    tblAssetCustom.Manufacturer Like '%dell%'
        OR tblAssetCustom.Manufacturer Like '%microsoft%'
      )
  AND tblAssetCustom.Model <> 'Virtual Machine'
  AND tblADComputers.OU Not Like '%denver%'
  AND tblComputersystem.Domainrole < 2

if Custom2 is, say, "Retired", then

(    tblAssetCustom.Custom2 Not Like '%retired%'      => false
  OR tblAssetCustom.Custom2 Not Like '%secondary%'    => true
  OR tblAssetCustom.Custom2 Is Null                   => false
)

Since your condition is "If A or B or C", because B is true the block evaluates as true.

Assuming what you're after is filtering out a record if Custom2 contains either "retired" or "secondary", you might want to reformat that something like

  AND (
        tblAssetCustom.Custom2 Is Null
        OR (     tblAssetCustom.Custom2 Not Like '%retired%'
             AND tblAssetCustom.Custom2 Not Like '%secondary%'
           )
      )