Return values if any record does not exist

Question

I have the tables:

tabelaUm
[id] [nome]
1    nome1
2    nome2

tabelaDois
[id] [idTabelaUm] [nome]
1    1            nome1
1    1            nome2
1    1            nome3
1    2            nome4
1    2            nome5
1    2            nome6

My query:

SELECT t1.nome AS n1, t2.nome AS n2
FROM tabelaUm AS t1
LEFT JOIN tabelaDois AS t2 ON 1
WHERE t1.nome = :foo
AND t2.nome = :bar

Upshot:

(':foo' => 'nome1', ':bar' => 'nome3') retorna 'n1' => 'nome1', 'n2' => 'nome3'
(':foo' => 'nome2', ':bar' => 'nome7') retorna empty
(':foo' => 'nome3', ':bar' => 'nome6') retorna empty
(':foo' => 'nome4', ':bar' => 'nome9') retorna empty

It is possible to make it return as follows?

(':foo' => 'nome1', ':bar' => 'nome3') retorna 'n1' => 'nome1', 'n2' => 'nome3'
(':foo' => 'nome2', ':bar' => 'nome7') retorna 'n1' => 'nome2', 'n2' => empty
(':foo' => 'nome3', ':bar' => 'nome6') retorna 'n1' => empty, 'n2' => 'nome6'
(':foo' => 'nome4', ':bar' => 'nome9') retorna 'n1' => empty, 'n2' => empty

Answer 1

I don’t know how to declare the Join clause how you used it (tabelaUm AS t1 LEFT JOIN tabelaDois AS t2 ON1), but, ignoring this, the result is not according to what you expect because although you declare Join as LEFT, in the clause WHERE you are imposing that T2.NOME accurate have a value and that it must be equal to :bar. In practice this contradicts the left previously declared and DBMS simply converts its LEFT JOIN in a common Join for optimization, according to the documentation.

To circumvent, just include in the clause WHERE the possibility of T2.Nome not exist:

SELECT t1.nome AS n1, t2.nome AS n2
FROM tabelaUm AS t1
    LEFT JOIN tabelaDois AS t2 ON t1.id = t2.idTabelaUm
WHERE t1.nome = :foo
    AND (t2.nome is null OR t2.nome = :bar)