Select with reference in several tables

Asked

Viewed 100 times

0

I have three tables: PRODUTOS, PEDIDO and ESTOQUE, and I want to list all the stock by order and by products, as shown below:

products

codigo    tipo      medida  
3020   |  unico  |    3  
3021   |  unico  |    5  
3022   |  unico  |    7  
3023   |  unico  |    3

request

codigo   qdtpedido  
3020   |    10  
3021   |    20  
3022   |    10  
3020   |    5  
3021   |    3  
3022   |    5

stockpile

codigo   qdtestoque  
3020   |    200  
3021   |    150  
3022   |    50  
3023   |    120

Select result

codigo  qtdestoque   tipo      medida      qtdpedido
3020   |   185   |   unico  |    3     |      15      |      
3021   |   127   |   unico  |    5     |      23      |      
3022   |   35    |   unico  |    7     |      15      |      
3023   |   120   |   unico  |    3     |      0       |

What I’m trying to do: 

SELECT e.*, 
(SELECT sum(qtdpedido) from pedido p where p.modelo = e.codigo) as qtdpedido,   
(SELECT tipo from produtos m where m.codigo = e.codigo) as tipo  
from estoque e ORDER BY codigo ASC;

As a result I need to list Stock (in which it contains all products), bringing the "type" and "measure" field that comes from the Products table and adding the "qtdpedidos" referring to that code.

Could someone help me?

mysql sql select

  • What you need is to use clauses INNER JOIN, LEFT JOIN and RIGHT JOIN https://www.devmedia.com.br/clausulas-inner-join-left-join-e-right-join-no-sql-server/18930

  • Your tables are a little confused... List the table produtos with the estoque but you don’t have a table itens_pedidos? There are more fields you didn’t report?

2 answers

2

Its structure is a little confused, but from what I understand, the three tables have the column codigo, which would be the field of union between them. Therefore, you must bring the columns p.codigo, p.tipo, p.medida that will be repeated for the items and make a sum of orders and stocks, based on these repeatable data:

SELECT p.codigo, p.tipo, p.medida,
   (SELECT SUM(p2.qtdpedido) FROM pedido p2 WHERE p2.codigo = p.codigo) as qtdpedido,
   (SELECT SUM(e2.qdtestoque) FROM estoque e2 WHERE e2.codigo = p.codigo) as qtdestoque
FROM produto p
LEFT JOIN estoque e
GROUP BY e.codigo, p.tipo, p.medida
ORDER BY codigo ASC;

  • rLinhares, thanks for the reply. I edited what I need as a result. It’s very similar to what you said up there. I’m not using id here, just code. As a result I need to list Stock (in which it contains all products), bringing the field type and measure that comes from the Products table and adding the orders referring to that code.

  • @rafB good to have helped =] but the ideal is that your question does not go unanswered, so I point out three alternatives: 1. change this response to accept it 2. help me edit this so it is accepted =p 3. create an answer

  • 1
    1. I will create an answer, but some corrections are required since it presented the following error: check the manual that Corresponds to your Mysql server version for the right syntax to use near 'ORDER BY e.asc code'

0

Below follows the proposed solution: 

SELECT e.*,
(SELECT SUM(p.qtdpedido) FROM pedido p WHERE p.codigo = e.codigo) as qtdpedido,
(SELECT tipo FROM produtos m WHERE m.codigo = e.codigo) as tipo
FROM estoque e
LEFT JOIN produtos m 
ORDER BY e.codigo ASC;

Browser other questions tagged mysql sql select

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