Select with reference in several tables

Question

Select with reference in several tables

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I have three tables: PRODUTOS , PEDIDO and ESTOQUE , and I want to list all the inventory going by order and by product, as I show 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

stock

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 Inventory (which contains all products), bringing the field "type" and "measure" that comes from the Products table and adding the "orders" for that code.

Could anyone help me?

mysql sql select

asked by anonymous 27.11.2018 / 17:52

2 answers

Jquery validate with type button Read char array from a binary file

score 2 · Answer 1

Its structure is a bit confusing, but as I understand it, the three tables have the column codigo , which would be the union field between them. So you have to 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;

score 0 · Answer 2

Below is 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;