CODEWITHSUNDEEP
sqlpostgresql
Leonardo Sarra
Leonardo Sarra

Reputation: 51

SQL exist clause is always true

I'm learning SQL using PostgreSQL as DBMS and I'm having some problem with a query that seems easy. I'm supposed to find apps that have their language or their year different from all the others app. The App table has 3 attributes: id(unique for each app),year,language

That's my solution which is not working (no result):

select p.id, p.language, p.year
from app p 
where not exists (select *
             from app p2
             where (p.id!=p2.id) AND (p.language=p2.language OR p.year=p2.year))

There are the entries in my table:

These

Upvotes: 1

Views: 384

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Do this as two separate comparisons:

select p.id, p.language, p.year
from app p 
where not exists (select 1
                  from app p2
                  where p.id <> p2.id AND p.year = p2.year
                 ) or
      not exists (select 1
                  from app p2
                  where p.id <> p2.id AND p.language = p2.language
                 ) ;

The problem with combining them into one statement is that either comparison overrides the other. So, if another row exists with a duplicate language, then you have a matching row and the not exists filter takes effect. There is no need to even look at year.

Upvotes: 1

Related Questions