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

Postgresql – Find a value based on all its present combinations with a value in another field in Postgres

I have a PostgreSQL table:

create table test(type_id, m_id)as values
 (1,       123)
,(2,       456)
,(3,       123)
,(4,       123)
,(2,       456)
,(1,       789)
;

Basically, one m_id could have multiple rows in this table associated with different type_ids.

How can I find m_ids that have type_ids only of value 2? In this example, it’s the m_id of 456 only.

I tried to group by m_id, but didn’t really work out well. Any ideas please?
Thanks!

Tags:

2

Answers


  1. 0

    You’re on the right track with the group by idea. Just add having clause to that:
    demo at db<>fiddle

    select m_id from test
group by m_id having every(type_id=2);
    m_id
    456

    The every() / bool_and() function checks a condition for all rows in the group so it makes sure all type_id‘s appearing with a given m_id are a 2. Or, that there doesn’t exist a row in that group for which that expression evaluates to false.

    You can also use an exists:

    select distinct m_id from test t1 
where type_id=2
  and not exists(select from test t2
                 where t1.m_id=t2.m_id
                 and t2.type_id<>2);

    Or an anti-join:

    select distinct t1.m_id
from test t1 
left join test t2 
 on t1.type_id=2
and t2.type_id<>2
and t1.m_id=t2.m_id
--that's the `anti-` part:
--it's a `left join`, but you then ask only for unmatched rows
--marked by a null on the right
where t2.m_id is null
  and t1.type_id=2;

    Or an except:

    select m_id from test where type_id=2
except
select m_id from test where type_id<>2;

    Here’s a test where there’s 13 such m_id‘s buried among 200k other in a heap of 900k randomised rows. It shows that both exists and the explicit anti-join examples result in a nested loop anti-join plan that outperforms the rest, at just 3ms. That’s thanks to these covering indexes:

    create index on test(type_id,m_id);
create index on test(m_id)include(type_id);

    Having needed 253ms and except took 530ms, even though they had also been given adequate indexes.

    Login or Signup to reply.
  2. 0 
    select m_id 
from yrtable
group by m_id 
having min(type_id)=2 AND max(type_id)=2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search