Vijay Anand Vijay Anand - 5 months ago 8
SQL Question

How to compare values in the same table

Filename||status
---------------
A|10
A|22
B|10


My expected output is

B|10


I should not get output whose file name is both 10 and 22 status

Answer

Murelink answer is correct. Another way to obtain the same correct result is to use MINUS operator: the first query is your own query, the second query subtract all records you don't want to see:

WITH files AS(
    SELECT 'A' AS filename, 10 AS status FROM dual UNION ALL
    SELECT 'A' AS filename, 22 AS status FROM dual UNION ALL
    SELECT 'B' AS filename, 10 AS status FROM dual 
)
    SELECT filename
    FROM files
    WHERE status = 10
MINUS
    SELECT filename
    FROM files
    WHERE status = 22;

Note that I don't extract the file status in the query: this is mandatory to make the MINUS operator work, but question is why do you need it? You know in advance that all returned records have status = 10