INNER JOIN of tables does not appear the values of their columns

Asked

Viewed 184 times

0

I’m making a INNER JOIN that joins the columns:

  • cd_devolucao table devolucao
  • cd_itens_venda table itens_venda
  • cd_produto table produto
  • valor_total table itens_venda

I’m wanting to do the INNER JOIN of these columns in the table itens_devolucao gathering the others that have foreign keys within the table itens_devolucao. Only that are returning all of them empty.

The mistake is that when a register is inserted in the table itens_devolucao INNER JOIN works, only I wanted INNER JOIN to work even with the empty table because I need to use data request in JSON.

inserir a descrição da imagem aqui

SELECT devolucao.cd_devolucao, itens_venda.cd_itens_venda, 
produto.cd_produto, itens_venda.valor_total FROM itens_devolucao
INNER JOIN devolucao ON (devolucao.cd_devolucao = itens_devolucao.cd_devolucao)
INNER JOIN itens_venda ON (itens_venda.cd_itens_venda = itens_devolucao.cd_itens_venda)
INNER JOIN produto ON (produto.cd_produto = itens_devolucao.cd_produto);
mysql

  • And your table itens_devolucao is empty? Is the definition of foreign keys correct? Post the definition of the tables involved and not just the drawing.

  • Related: What is the difference between INNER JOIN and OUTER JOIN?

  • I do not understand what your idea is. If there is no record itens_devolucao you want to return the Cartesian product from the other tables?

  • @anonym exactly that, even though there is no record of itens_devolucao i want to return the INNER JOIN from the other tables.

  • 1

    @Vorbbel: You’d better review the concept of INNER JOIN.

  • See if this is what you want: FROM itens_devolucao
RIGHT OUTER JOIN devolucao ON (devolucao.cd_devolucao = itens_devolucao.cd_devolucao)
RIGHT OUTER JOIN itens_venda ON (itens_venda.cd_itens_venda = itens_devolucao.cd_itens_venda)
RIGHT OUTER JOIN produto ON (produto.cd_produto = itens_devolucao.cd_produto)

  • @anonimo only returned the record of cd_produto, because I deleted the record of itens_devolucao that there was only one.

  • I could not understand what you want but the Cartesian product of your tables you get with: SELECT devolucao.cd_devolucao, itens_venda.cd_itens_venda, 
produto.cd_produto, itens_venda.valor_total 
FROM itens_devolucao, devolucao, itens_venda, produto;

  • @anonym the way you spoke now realize how unnecessary was the use of INNER JOIN in this code, I had not realized it.

  • But still does not return the column records.

Show 5 more comments

1 answer

1


I recommend not using the quotes on ON of SELECT, and when you list all the tables at once and you don’t find the result you probably don’t have any item that is in all the tables, because not all sales have returns for example, in this case would use the LEFT JOIN that, if there is no value of a given table behind as it is NULL and does not exclude the search result:

SELECT devolucao.cd_devolucao, itens_venda.cd_itens_venda, 
produto.cd_produto, itens_venda.valor_total 
FROM itens_devolucao
LEFT JOIN devolucao 
  ON devolucao.cd_devolucao = itens_devolucao.cd_devolucao
LEFT JOIN itens_venda 
  ON itens_venda.cd_itens_venda = itens_devolucao.cd_itens_venda
LEFT JOIN produto 
  ON produto.cd_produto = itens_devolucao.cd_produto;

Your query will bring all the items that were returned and all the information that are in other tables if there is, if you want to search all the products with their respective "movements" use the product table on FROM or use the concept of OUTER JOIN.

  • He hasn’t returned anything yet.

  • Find out the error, when an itens_sell table insert occurs INNER JOIN works by returning all columns with the records.

  • But I didn’t want it, I wanted INNER JOIN to work even with the table itens_venda empty.

  • You can define the conditions through the WHERE, even if everything is through the LEFT JOIN, for example: WHERE itens_venda.cd_itens_venda IS NOT NULL

  • @Vorbbel: You’d better review the concept of INNER JOIN.

Browser other questions tagged mysql

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