Home > Mobile >  Why it is not filtering? SQL QUERY "Where"
Why it is not filtering? SQL QUERY "Where"

Time:10-05

In this code, I wanted to filter the "state_id" with only 3 ids using "where" keyword but it didn't work, it showed everything.

here is the code:

SELECT p.name as "patient", at.[RAPID3 Name], at.[DLQI Name], at.[HAQ-DI Name], p.ID, phy.Name, at.State_ID
FROM [ABV HUM Reporting].dbo.[Assessment Tool] as at
left join [ABV HUM Reporting].dbo.Patient as p on p.[ID] = at.[Owner (Patient)_Patient_ID]
left join [abv hum reporting].dbo.[Physician] as phy on phy.ID = p.Physician_ID
--left join [ABV HUM Reporting].dbo.[Physician] on p.Physician_ID = phy.Name
where at.State_ID=168 or at.State_ID = 165 or at.State_ID = 162
and at.[RAPID3 Name] is not null or at.[DLQI Name] is not null or [HAQ-DI Name] is not null Order by at.date DESC 

a screenshot of the results:

enter image description here

CodePudding user response:

 where (at.State_ID=168 or at.State_ID = 165 or at.State_ID = 162)
and (at.[RAPID3 Name] is not null or at.[DLQI Name] is not null or [HAQ-DI Name] is not null )

CodePudding user response:

and has a higher precedence than or, which is causes the condition to not be evaluated like you're (probably) expecting it to be. A couple of parentheses would solve the issue:

where (at.State_ID=168 or 
       at.State_ID = 165 or 
       at.State_ID = 162
      )
      and 
      (at.[RAPID3 Name] is not null or 
       at.[DLQI Name] is not null or 
       [HAQ-DI Name] is not null
      )

Alternatively, you can rewrite the condition using in and coalesce, which would be a lot cleaner:

where at.State_ID IN (168, 165, 162)
      AND
      COALESCE (at.[RAPID3 Name], at.[DLQI Name], [HAQ-DI Name]) IS NOT NULL

CodePudding user response:

Operator precedence is in play here. (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver15)

You can force SQL Server to take something with a higher precedence (or group things together if you wish) by using parenthesis.

where
      (at.State_ID=168 or at.State_ID = 165 or at.State_ID = 162)
  and (at.[RAPID3 Name] is not null or at.[DLQI Name] is not null or [HAQ-DI Name] is not null)
  • Related