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:
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)