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

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;

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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