Select from cross tables in postgres problem

Question

Lets imagine i have a table A that contains rows X,Y and Z, and i have another table that relate elements from A to another table B.

- A)
ID | name
01 | X
02 | Y
03 | Z

- B)
ID | name
01 | b


- A_B)
ID | A_ID | B_ID
01 | 01 | 01

A_B : element_A, element_B, and i want a query that for element b in B returns for all elements a in A return True if {a, b} exist in the table A_B and False if not

Result of b in B
 A.name | Value
 X      | True
 Y      | False
 Z      | False

OR 
 A.name | B.ID
 X      | 01
 Y      | null
 Z      | NULL

and that's what i tried so far.

SELECT *
from A
         LEFT JOIN A_B ei ON A.id = A_B.a_id

Answer 1

You can cross join tables a and b to generate all possible combinations, then bring bridge table a_b with a left join:

select a.name, (ab.id is not null) as is_in_ab
from a
cross join b
left join a_b ab on ab.a_id = a.id and ab.b_id = b.id
where b.name = 'b'

You could also use exists and a correlated subquery:

select 
    a.name, 
    exists (select 1 from a_b ab where  ab.a_id = a.id and ab.b_id = b.id) as is_in_ab
from a
cross join b
where b.name = '2'