I have two tables A and B where the relationship is one to many (A -> many B). Table "A" contains columns id, name and table "B" has id, a_id(fk), is_off(boolean).
Now, I want to get id of "A" which has all "B"'s is_off = true.
I tried this one select a.id from A a inner join B b on a.id = b.a_id where b.is_off = true But it only returns even if an "A" has an item (B) which has is_off = false;
Any help will be appreciated.
You were close. A subquery is probably what you're looking for:
Test data
CREATE TABLE a (id int PRIMARY KEY, name text);
CREATE TABLE b (id int, a_id int REFERENCES a(id), is_off boolean);
INSERT INTO a VALUES (1,'foo');
INSERT INTO b VALUES (42,1,true),(1,1,false);
Your query would return all records if at least one of the b records fulfil your join and where clauses:
SELECT * FROM a
JOIN b on a.id = b.a_id
WHERE b.is_off;
id | name | id | a_id | is_off
----+------+----+------+--------
1 | foo | 42 | 1 | t
(1 Zeile)
If you intend to exclude all a records that contain at least one is_off = true, you can use NOT IN with a subquery, but as suggested by @a_horse_with_no_name (see comments below) you could use EXISTS or NOT EXISTS:
SELECT * FROM a
WHERE NOT EXISTS (SELECT a_id FROM b WHERE a.id = b.a_id AND is_off);
id | name
----+------
(0 Zeilen)
