Relationship of two MYSQLI tables

Asked

Viewed 73 times

0

I’m trying to link two tables so the result looks like this;

Address for test of the query http://sqlfiddle.com/#! 9/d9acad/1

inserir a descrição da imagem aqui

I tried to use the command below;

SELECT 
    item_inventarios.sku,
    item_inventarios.cod_setor,
    inventario_ambientes.setor_ini,
    inventario_ambientes.setor_fim,
    inventario_ambientes.desc_ambiente
FROM 
    item_inventarios,inventario_ambientes
WHERE 
    item_inventarios.`id_inventario`='46' 
and inventario_ambientes.id_inventario = item_inventarios.id_inventario

The result is generating so; inserir a descrição da imagem aqui

Table inventory_environments inserir a descrição da imagem aqui Table item_inventarios inserir a descrição da imagem aqui

sql

  • Some guidance on your question: 1) Explain better the expected end result. What is the relationship? What is the relationship between the data pointed out? 2) Add to your question what you have already tried to do, even if it has not worked. So we have a point to break from. 3) Instead of inserting images, enter the data. It makes it easy for us to copy and help with a response.

  • Thank you! I’ve adjusted the question.

  • Still left to put what has as image as text so that we can reproduce your code.

  • Here. you can test my database; http://sqlfiddle.com/#! 9/d9acad/1

1 answer

1


Correct me if I’ve misunderstood the question, but I think you need this:

SELECT 
    ii.sku,
    ii.cod_setor,
    ia.setor_ini,
    ia.setor_fim,
    ia.desc_ambiente,
    ii.cod_coletor
FROM 
    item_inventarios ii
inner join inventario_ambientes ia on ii.id_inventario = ia.id_inventario
    and ii.cod_setor between setor_ini and setor_fim
WHERE 
    ii.id_inventario = '46' 
order by 1

  • I edited the question to make it more understandable.

  • I don’t understand the logic of the result you want. What exactly you need?

  • I need to include the column desc_ambiente of tabela inventario_ambientes backbone cod_setor table item_inventarios, but the relationship between cod_setor with the table desc_ambiente is among setor_ini and setor_fim Example Table inventario_environments I have the cod_setor= 1 and 2 and would find the same values in the column setor_ini and setor_fim of the table inventory_ambientes

  • I edited the query, make sure it’s ok

  • Perfect ! it worked

Browser other questions tagged sql

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