Return values if any records do not exist

Navigation
0

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

Result: 

(':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

Can you make it come back 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
    
asked by anonymous 03.05.2018 / 04:31

1 answer

0

I'm not sure how to declare the join clause as you used it ( tabelaUm AS t1 LEFT JOIN tabelaDois AS t2 ON 1 ), but ignoring this, the result is not in agreement with what you expect because even though you declare the join as LEFT , in the WHERE clause you are imposing that T2.NOME need to have a value and that it must be equal to :bar . In practice this contradicts the previously declared left and the DBMS simply converts its LEFT JOIN to a common join for optimization, according to the documentation .

To get around, simply include in the WHERE clause the possibility of T2.Nome not to 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)
    
03.05.2018 / 04:55
"Primary script unknown" while reading response header from upstream - NGINX Open only one Crystal screen (preview) - Spring BSS