how to get the results from table A where the data in table B are only true-CodePudding

how to get records from two tables where all for example statuses are true in the second table.

Table A                    Table B
id   name           id   idA  status
9    'name1'        20   9    true
                    21   9    false
7    'name2'        22   7    true
                    23   7    true
                    24   7    true
6    'name3'        30   6    false
                    31   6    true
                    32   6    false
output
result: 
table A             tableB
7    'name2'        22   7    true
                    23   7    true
                    24   7    true

I would like to get the results from table A where the data in table B are only true

CodePudding user response：

This can be solved in two ways:

Having "only true" means that there should not be any status with false in table b:

select a.*
from table_a a
where not exists (select *
                  from table_b b
                  where b.ida = a.id
                  and not status)

Alternatively one can use the aggregate function bool_and() to find those rows that have only true values for the status column:

select a.*
from table_a a
where exists (select b.ida
              from table_b b
              where b.ida = a.id
              group by b.ida
              having bool_and(status))

CodePudding user response：

For your question I will use NOT EXISTS operator like this :

   select ta.id, ta.name, tb.id, tb.idA, status
    from tableA ta
    LEFT JOIN tableB tb On ta.id = tb.idA
    WHERE NOT EXISTS ( select idA
    from tableB tbb
    where status = 'false' and tbb.idA = ta.id);

it will not take any id that has false at least once

CodePudding user response：

You can use the aggregation function bool_and for this purpose. If it returns true for a given idA, the corresponding record in tableA must be returned.

SELECT * FROM tableA
WHERE id IN (SELECT idA from tableB GROUP BY idA HAVING bool_and(status))

The magic is in the HAVING bool_and(status) which means "keep the idA for which true (implicit) is returned after aggregation".