Left outer Join does not have Null values [duplicate]

Question

Left outer Join does not have Null values [duplicate]

Navigation

#1 by (2 votes)

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 the linked records of the fields and their values are as follows:

input_name, Fagner, 2
input_sexo, M, 2

To get the filled values from the formulário_valor table I make a main select on the formulario_campo table and then make a left outer join on the formulario_valor table to bring the 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'

This select returns me the following:

Doubt:addingnewfieldstotheform_fieldtable,forexampleIhavenowaddedanewfieldcalled:input_data_birth,logicallythisfieldhasnovaluesregisteredintheformulario_valortable,theselectthatI'mdoingaboveshouldnotreturnmeallfieldvaluesthatIhaveregisteredinthe"field_format" table and the input_data_name record 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 join query select

asked by anonymous 21.01.2016 / 14:40

1 answer

How to put the current date and time in a datetime field? [closed] How to leave a div and an image over a carousel?

score 2 · Answer 1

Dude is hard to understand your problem, maybe you could improve the description of your tables and data. If I understood correctly, you are not seeing some data because of the Where clause. You should add "OR b.cod_oportunidade IS NULL" to have the result that I understood to be 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 join conditions of the tables and not the filters we want for the query. The filters should come in the 'WHERE' clause