Tables with a ManyToOne relationship, how to select rows from master if none of the linked rows fulfill a condition?

Admin 1
Viewed 29

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?

postgresql sql
1 Answers

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
user_10018840
Related