Select with INNER JOIN, bringing fields that have foreign key NULL

Question

Select with INNER JOIN, bringing fields that have foreign key NULL

Navigation

#1 by (4 votes)
#2 by (3 votes)

2

I have a table of items, request_itens, interested (it has auto relationship, it is used for secretary, sector and employee, and a requested table), in the order table I have 3 foreign keys (one for secretariat, another for sector and one more for official), where the foreign registry key is always filled, and the others are optional, getting null when they are not filled.

I need to create a select that will bring me a result even when those foreign keys to sector and employee in the requested table are NULL.

The select I just made shows me the records that have value in the command_id, set_id and function_id in the requested table:

SELECT pedido.id as id_pedido, pedido.id_secretaria, pedido.id_setor, pedido.id_funcionario, pedido.dataretirada, 
   pedido_itens.id_item, pedido_itens.quantidade,  pedido_itens.datadevolucao,
   item.nome as item_nome,  
   secretaria.nome as secretaria,
   setor.nome as setor,
   funcionario.nome as funcionario
   FROM pedido 
   INNER JOIN pedido_itens ON (pedido.id = pedido_itens.id_pedido)
   INNER JOIN item ON (pedido_itens.id_item = item.id)
   INNER JOIN interessado as secretaria ON (pedido.id_secretaria = secretaria.id)
   INNER JOIN interessado as setor ON (pedido.id_setor = setor.id) 
   INNER JOIN interessado as funcionario ON (pedido.id_funcionario = funcionario.id);

mysql sql join select inner

asked by anonymous 27.05.2016 / 23:21

2 answers

3

There is the option you can use LEFT JOIN , take a look at this post click here , you would still need to check your business rule, / p>

27.05.2016 / 23:40

Convert ZIP to latitude and longitude (Javascript) Catch only first word inside a sql server query

score 4 · Accepted Answer

Use left join instead of inner join

select pedido.id as id_pedido, pedido.id_secretaria, pedido.id_setor, pedido.id_funcionario, pedido.dataretirada, 
   pedido_itens.id_item, pedido_itens.quantidade,  pedido_itens.datadevolucao,
   item.nome as item_nome,  
   secretaria.nome as secretaria,
   setor.nome as setor,
   funcionario.nome as funcionario
   FROM pedido 
   INNER JOIN pedido_itens ON (pedido.id = pedido_itens.id_pedido)
   INNER JOIN item ON (pedido_itens.id_item = item.id)
   LEFT JOIN interessado as secretaria ON (pedido.id_secretaria = secretaria.id)
   LEFT JOIN interessado as setor ON (pedido.id_setor = setor.id) 
   LEFT JOIN interessado as funcionario ON (pedido.id_funcionario = funcionario.id);