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
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
)