Question 89993 in PostgreSQL Question posted in
The official documentation can be found here.

how to get the results from table A where the data in table B are only true – Postgresql

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

Tags:

3

Answers


  1. 0

    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".

    Login or Signup to reply.
  2. 0

    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))
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search