How to use the "Or" statement when having multiple conditions in the Where clause in SQLit-CodePudding

I am trying to retrieve data that has "Positive" result and the code set that contains any of these codes: [U071, R12, Z515, R509] as my desired goal for Table 1. I have tried using "or" but it kept giving me a mixture of negative and positive results instead of positive results only. I hope my question is clear as I am fairly new here.

Desired goal for Table 1:

ID	START_DATE	TEST RESULT	Code	END_DATE
1	03-31-2021	positive	U071	03-31-2021
2	06-06-2020	positive	R12	06-09-2020
2	06-06-2020	positive	Z515	06-10-2020
3	06-30-2021	positive	R509	07-02-2021
4	08-08-2020	positive	U071	08-10-2020
4	08-09-2020	positive	Z515	08-10-2020

Note: I would also like ID 2 to be grouped as 1.

The problem:

ID	START_DATE	TEST RESULT	Code	END_DATE
1	03-31-2021	positive	U071	03-31-2021
2	06-05-2020	negative	R12	06-09-2020
2	06-05-2020	negative	Z515	06-09-2020
3	06-03-2021	positive	R509	07-02-2021
4	08-10-2020	negative	U071	08-10-2020
4	08-06-2020	positive	Z515	08-10-2020

My code:

CREATE TABLE table1 AS
select   ID
        ,TEST_RESULT  
        ,START_DATE
        ,END_DATE
        ,Code
from table0
where TEST_RESULT = 'POSITIVE' AND Code = 'U071' OR Code = 'Z20828' OR DIAGNOSIS_CD = 'Z20822' OR DIAGNOSIS_CD = 'R112' OR DIAGNOSIS_CD = 'R509'
group by ID, START_DATE
ORDER BY ID;

CodePudding user response：

I think the real issue is you did not use ( after the and. But you can use IN to make it clearer -- like this:

where TEST_RESULT = 'POSITIVE' AND (Code IN ('U071','Z20828') OR DIAGNOSIS_CD IN ('Z20822','R112','R509'))