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