How to search for same ID with different test results in SQLite?

user_19729395 0
Viewed 46

My goal is to write a code to find the same ID that has different test results. Table 1 contains many rows, but I added these 4 rows as an example. I know my code is not correct, but what would be the correct way of obtaining this specific information? (see Desired Goal below) I hope my question makes sense, but feel free to comment if it is not clear.

Table 1:

ID TEST_RESULT DIAGNOSIS
2 POSITIVE X78
2 NEGATIVE U98
3 POSITIVE U98
4 NFEGATIVE Y55

Desired Goal:

ID TEST_RESULT DIAGNOSIS
2 POSITIVE X78
2 NEGATIVE U98

Code:

SELECT *
FROM table1
WHERE ID <> ' ' AND TEST_RESULT= NEGATIVE AND TEST_RESULT = POSITIVE;
sqlite sql
3 Answers

Just use EXISTS operator to check if another row with the same id, but different test_result values exists in the table.

SELECT
    *
FROM results r1
WHERE EXISTS (
    SELECT 1
    FROM results r2
    WHERE r1.id = r2.id AND r1.test_result != r2.test_result
)

Output

id test_result diagnosis
2 POSITIVE X78
2 NEGATIVE U98

db<>fiddle here

user_58768030 
select   ID 
        ,TEST_RESULT    
        ,DIAGNOSIS
from    (
         select   *
                 ,min(TEST_RESULT) over w as mn
                 ,max(TEST_RESULT) over w as mx
         from     t
         window   w as (partition by ID)
        ) t
where    mn <> mx
ID TEST_RESULT DIAGNOSIS
2 POSITIVE X78
2 NEGATIVE U98

Fiddle

user_191745700

You will need a sub-query to count how many POSITIVE and NEGATIVE results each ID has and then use its results in your filtering:

SELECT *
FROM Table1
WHERE id IN (
              SELECT ID
              FROM Table1
              GROUP BY ID
              HAVING SUM(CASE WHEN test_result = 'POSITIVE' THEN 1 ELSE 0 END) > 0
                     AND  
                     SUM(CASE WHEN test_result = 'NEGATIVE' THEN 1 ELSE 0 END) > 0   
             );

FIDDLE DEMO

user_20559980
Related