Reputation: 51
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:
Upvotes: 1
Views: 384
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