Tables with a ManyToOne relationship, how to select rows from master if none of the linked rows fulf-CodePudding

I have two tables:

foos:

id	f_name
1	xxx
2	yyy
3	zzz

foo_bars:

id	foo_id	active
1	1	false
2	1	false
3	1	false
4	2	true
5	2	false
6	2	true
7	3	false
7	3	true

Each foo should have each an active foo_bar, and only one. Because of a poorly designed process, it sometimes happened that either more than one foo_bar was set to active=true, or that no foo_bar was set to active. Neither is a valid scenario.

I need to find those rows on foos where the linked foo_bars rows with active are != 1.

E.g. in the above scenario I'd like to get:

1,xxx
2,yyy

Can I accomplish this with a single query?

CodePudding user response：

I think this would be a good use of the HAVING clause in a GROUP BY query:

select
  f.id, f.f_name
from foos f
left join foo_bars b on f.id = b.foo_id
group by
  f.id, f.f_name
having
  count (*) filter (where b.active) != 1

CodePudding user response：

You should be able to do this with an outer join and simple aggregation:

select
*
from
(
select
foo.id,
coalesce(count (foo_bar.id),0) as id_count
from
foo
left join foo_bar
on foo.id = foo_bar.foo_id
group by
foo.id
) t
where id_count <> 1

db fiddle