Selecting only if on column value is distinct by other column

Question

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

forpas · Accepted Answer · 2020-07-05 07:54:20Z

3

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 )

answered Jul 5, 2020 at 7:54

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DSCH Over a year ago

Thanks @forpas , where would I add where rule_id = <some_rule_id> tried for both with 1 according to my example and I get my entire table minus the a 2nd row

forpas Over a year ago

Why the entire table minus the a 2nd row? What is the logic to exclude that row? Its sub_rule_id is 1 just like the 1st row. So they should both be excluded. The same for rows 4 and 5.

DSCH Over a year ago

Thanks! Managed with the NOT EXISTS alternative

Collectives™ on Stack Overflow

Selecting only if on column value is distinct by other column

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related