sql - Postgres Relationship query

Question

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.

Answer 1

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)