Left Outer Join does not bring Null values

Asked

Viewed 1,233 times

2

I have two tables:

formulario_campo ( cod_campo, descricao, cod_formulario )

Filled with the following values

- input_nome, Nome, 1
- input_sexo, Sexo, 1

The second table:

— formulario_valor(cod_campo,valor,cd_oportunidade)

In this table are the linked records of the fields and their respective values, as follows:

  • input_name, Fagner, 2
  • input_sex, M, 2

To bring the filled values from the table formulário_valor i make a main select in table formulario_campo and then I make a left outer join on the table formulario_valor to bring value:

SELECT 
  a.`cod_campo`,
  a.`descricao`,
  b.valor,
  b.`cod_formulario`,
  b.`cod_oportunidade` 
FROM
  formulario_campo a 
  LEFT OUTER JOIN formulario_valor b 
    ON (
      a.`cod_campo` = b.`cod_campo` 
      AND a.`cod_formulario` = b.`cod_formulario`
    ) 
WHERE b.cod_oportunidade = 145 
ORDER BY a.`ordem`

That select brings me back:

inserir a descrição da imagem aqui

Doubt: adding new fields in the formula_field table, for example now I added a new field called: input_birth date, logically this field has no values recorded in the table formulario_valor, the problem is being this point, the select what I am doing above should not return me all the field values I have recorded in the table "formulary_field" and the record input_birth date came with the value of NULL?

Attempts I made:

  • FULL OUTER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • UNION

But all this I could not bring the new fields that I add with null value...

mysql query select join

  • Try to put this instruction WHERE b.cod_oportunidade = 145 inside the ON of the LEFT JOIN. and b.cod_oportunidade = 145 . Must solve.

  • Whoa, thanks for the help, I tried to do it the same way, but it wasn’t...

  • There is no dynamic pro solution you want. You will have to adjust on the arm whenever you have a new field. If I understand your problem.

  • Hello @Darkhyudra thank you again, but the term "there is no solution" is incorrect, a solution exists yes, in our developer life everything is possible until the moment we think, but I believe something very silly is missing in this select, in summary I have a table B that has the registered values of table A, at a given moment I am taking all the records of table A and displaying the values of table B, but when I added a new record in table A that does not exist in table B the select above should return me the column Table value B as null, right?

  • One question: in select you have the new field?

1 answer

2

Dude is hard to understand your problem, maybe you could improve the description of your tables and data. If I understand correctly, you are not seeing some data because of the 'Where' clause. You should add "OR b.cod_opportunity IS NULL" to get the result I understood to be the expected. 

SELECT 
  a.cod_campo,
  a.descricao,
  b.valor,
  b.cod_formulario,
  b.cod_oportunidade 
FROM
  formulario_campo a 
  LEFT OUTER JOIN formulario_valor b 
    ON (
      a.cod_campo = b.cod_campo 
      AND a.cod_formulario = b.cod_formulario
    ) 
WHERE b.cod_oportunidade = 145 OR b.cod_oportunidade IS NULL
ORDER BY a.ordem

Just a hint: in the 'ON' clause we should put the junction conditions of the tables and not the filters we want for the query. The filters should be in the 'WHERE' clause'

  • Hello Pedro, thank you for your answer.. the Where Clause is required, in reality this Select is just a summary I did to illustrate better, the cod_opportunity will never be nulla, it is a code of a client. But in summary the select I’m doing should return me one more record, in the image I made in the post it returned me those records that already exist in the formulary_value table, but I inserted a new record in the formula_field table and technically when I did this select I should return this new field but column b.value would be null.

  • @Ronisommerfeld, the code proposed by @Pedroteles is correct. Despite using a left join, if you want to use a restriction in the Where clause in right-hand table you also need to contemplate the objectionability of the record to come null, otherwise the DBMS converts your left join for inner join implicitly.

Browser other questions tagged mysql query select join

You are not signed in. Login or sign up in order to post.