Question 272597 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres Query To Select a Column with the same value Where Values in another column are of a pre-defined subset

Given the table below, I’m trying to find a way to get the group matching a pre-defined subset of tokens BUT the group should not contain any tokens outside of the subset.

group token
3 37
1 34
1 32
3 32
3 34

Example: I want to select the group containing tokens (32, 34).
Problem: Doing a GROUP BY group and an IN (32, 34) condition will mean receiving both group 3 and group 1. However, group 3 should be omitted on the basis that it also has a token with value 37 which is not in my subset.

Efforts:

  1. Attempted string_agg on the token column with different comparison operators which check the list of group columns that were selected based on token matching one value in my subset (32, 34). This got messy very quickly and resulted in type conversions and elaborate string comparisons.

  2. Attempted a nested query which would first extract a list of group ids as long as one of the token values were in my list, and then attempting to count the total token belonging to group and comparing it to the length of my subset. Due to the implementation of this table and how it’s used, it would be a sufficient solution since I’m guaranteed no duplication of groups containing the specified token set but it is definitely not ideal.

Tags:

2

Answers


  1. 0

    Use a having clause to determine if the count of in(32,34) is greater tan 1 but also that the count for any other token is zero. e.g:

    select
  group_id
from your_table
group by
  group_id
having count(case when token in(32,34) then 1 end) > 0
and count(case when token not in(32,34) then 1 end) = 0
    group_id
    1

    fiddle

    nb "not in()" can produce unexpected results if it encounters a null so I usually avoid it and so I have avoided that through the where clause. Here it is is not essential as tested at the fiddle above. Another approach without the where clause:

    having count(case when token in(32,34) then 1 end) > 0
and count(case when coalesce(token,-1) not in(32,34) then 1 end) = 0
    Login or Signup to reply.
  2. 0

    If your set is in table s like below

    create table s as
select 34 token union all
select 32 token;

    And your table is t.

    You can left join table t with table s.

    select t.group_, s.token
from   t
       left join s
       on s.token = t.token;
    group_ token
    1 32
    3 32
    1 34
    3 34
    3 (null)

    You can see null in token column of s table. That null means 37 value not exists in s table.

    Now you need to exclude by changing null to -1 (or whatever value smaller than any values) and check it in having statement.

    select t.group_, min(coalesce(s.token, -1)) min_token
from   t
       left join s
       on s.token = t.token
group by t.group_
having min(coalesce(s.token, -1)) != -1;
    group_ min_token
    1 32

    http://sqlfiddle.com/#!17/cc1cb9/8

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search