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!
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.
1
select m_id
from yrtable
group by m_id
having min(type_id)=2 AND max(type_id)=2