CODEWITHSUNDEEP
sqlpostgresql
Zsolt Stegena
Zsolt Stegena

Reputation: 15

PostgreSQL selection

I have a table looks like:

user  key
x     1
x     2
y     1
z     1

The question is simple. How to find out which one is the user who has not key 2? The result should be y and z users.

Upvotes: 1

Views: 35

Answers (3)

MatBailie
MatBailie

Reputation: 86715

@jarlh's answer is likely the fastest if you have two tables;
- One with the users
- One with your facts 

select "users"."user_id"
from "users"
where not exists (select 1 from tablename t2
                   where t2."user_id" = "users"."user_id"
                     and t2."key" = 2)

That's the structure I would recommend too, having two tables.

For your case, where you only have one table, the following may be a faster alternative; it does not need to join or run a correlated sub-query, but rather scans the whole table just once.

SELECT
    "user"
FROM
    tablename
GROUP BY
    "user"
HAVING
    MAX(CASE WHEN "key" = 2 THEN 1 ELSE 0 END) = 0

Upvotes: 3

William
William

Reputation: 6610

Try this;

SELECT "user", "key"
FROM Your_Table AS T1
LEFT OUTER JOIN Your_Table AS T2 ON T1."user" = T2."user" AND T2."key" = 2
WHERE T2."user" IS NULL

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Return a user row as long as the same user doesn't have another row with key 2.

select user, key
from tablename t1
where not exists (select 1 from tablename t2
                  where t2.user = t1.user
                    and t2.key = 2)

Note that user is a reserved word in ANSI SQL, so you may need to delimit it as "user".

Upvotes: 2

Related Questions