sql - Many to one relation, select only rows which ancestor met all criteria -

first show architecture of tables.

 table "public.questionare"       column        |          type         |                            --------------------+-----------------------+  id                 | integer               |      table "public.questionareacceptance"          column          |          type         |                          -------------------------+-----------------------+  id                      | integer               |   questionare_id          | integer               |  accept_id               | integer               |  impact_id               | integer               | 

table questionareacceptance contains:

id  | questionare_id | accept_id|       impact_id  | ----+----------------+----------+------------------+ 1   |1               |1         |                  | 2   |1               |1         | 1                | 3   |1               |1         | 1                | 4   |2               |          | 1                | 5   |3               |1         | 1                | 6   |4               |1         | 1                | 7   |4               |1         | 1                | 

what trying list of questionare id in each questionareacceptance fields accept_id , impact_id not null

my query looks like:

select q.id quest,     qa.id accepted questionare q,     questionareacceptance qa q.id = qa.questionare_id     , qa.accept_id not null     , qa.impact_id not null; 

but result fallows:

      quest         |          accepted     |                            --------------------+-----------------------+  1                  |1                      |   1                  |2                      |   1                  |3                      |   2                  |4                      |   3                  |5                      |   4                  |6                      |   4                  |7                      |  

but result should returned 3 , 4 others have impact_id or accept_id null.

can point me doing mistake?

your query written not exists:

select     q.id quest, qa.id accepted questionare q     inner join questionareacceptance qa on qa.questionare_id = q.id     not exists (         select *         questionareacceptance tqa                    tqa.questionare_id = q.id ,            (tqa.accept_id null or tqa.impact_id null)     ) 

but think faster 1 using window functions:

with cte (     select         q.id quest, qa.id accepted,         sum(case when qa.accept_id not null , qa.impact_id not null 1 else 0 end) over(partition q.id) cnt1,         count(*) over(partition q.id) cnt2     questionare q         inner join questionareacceptance qa on qa.questionare_id = q.id ) select quest, accepted cte cnt1 = cnt2 

actually looks don't need join @ all:

with cte (     select         qa.questionare_id quest, qa.id accepted,         sum(case when qa.accept_id not null , qa.impact_id not null 1 else 0 end) over(partition qa.questionare_id) cnt1,         count(*) over(partition qa.questionare_id) cnt2       questionareacceptance qa ) select quest, accepted cte cnt1 = cnt2; 

sql fiddle demo