How to join 2 tables in a select?

Navigation
2

I have the following tables in my MySQL DB:

produtos_pedido with the following fields

  

id, order_id, product_id, qtd

produtos_troca with the following fields

  

id, order_id, product_id, qtd

I need to make a SELECT where I get the id_produto (with distinct without repeating the product) and the qtd (total, that is, summing the qtd of the same products), but I have to join the two tables. Is this possible in MySQL?

Can anyone help me with this SELECT ?

    
asked by anonymous 16.08.2016 / 12:58

3 answers

6

If I understand correctly you want to join the two tables and then select all products by id_produto and add up the quantities, then this will be: 

SELECT id_produto, SUM(qtd) as qtdTotal 
FROM (
      SELECT id, id_produto, qtd FROM produtos_pedido 
      UNION ALL 
      SELECT id, id_produto, qtd FROM produtos_troca
     ) res 
GROUP BY id_produto;

See it working

    
16.08.2016 / 15:53
2

You can work out as follows: 

SELECT 
    pp.id, 
    pp.id_pedido as id_pedido_pedido,
    pt.ltd  as quantidade
FROM produtos_pedido as pp
JOIN produtos_troca as pt USING(id_produto)

When you do foreach() you use echo $valor['quantidade'] to display the quantity of that product.

    
16.08.2016 / 13:04
1

If it is what I understood well, you have to add the column values (qtd), but not the same product id.

If this is the case, you can use the SUM () functions to sum the values, GROUP BY to group the data.

So; 

SELECT count(pp.id) as id, sum(pt.ltd) as TotalQtd
FROM produtos_pedido as pp
JOIN produtos_troca as pt
group by pp.id
    
16.08.2016 / 14:14
How to check memory usage in Visual Studio? Checking if value exists in an array via search field