Selecting only if on column value is distinct by other column

user_5751251 0
Viewed 31

I have a linking table with rule_id to sub_rule_id as such:

rule_id | sub_rule_id
---------------------
1       | 1
2       | 1
2       | 2
2       | 3
3       | 3
3       | 4

I want to be able to get all the sub_rule_ids which are linked to only one rule_is by rule_id. So if my rule_id = 1 then I expected no rows. And if rule_id = 2 then I should get just one. Tried to play with distinct and having and would not trouble you with a bad query.. I am sure there is an easy elegant way to do it.

Thanks in advance

postgresql sql
1 Answers

You can group by sub_rule_id amd set the condition in the having clause:

select sub_rule_id
from tablename
group by sub_rule_id
having count(distinct rule_id) = 1

Or with NOT EXISTS if you want full rows:

select t.* from tablename t
where not exists (
  select 1 from tablename 
  where sub_rule_id = t.sub_rule_id 
  and rule_id <> t.rule_id
)
user_104988280
Related