How to join 2 tables in a select?

Asked

Viewed 8,116 times

2

Guys I have the following tables in my BD Mysql:

produtos_pedido with the following fields

id,id_request,id_product,Qtd

produtos_troca with the following fields

id,id_request,id_product,Qtd

Well I need to make one SELECT where I return the id_produto (with distinct without repeating the product) and the qtd (total, that is by adding the Qtd of the equal products) but I have to join the two tables. This is possible in Mysql?

Could someone help me with this SELECT?

mysql

  • Do you want the total quantity of products in the two tables? Or the quantity of products per order/exchange?

  • the total quantity of products

3 answers

8


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

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 working

  • 1

    That’s what Jorge was, thank you very much.

2

You can elaborate 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 the foreach() you use echo $valor['quantidade'] to display the quantity of that product.

  • ok but I need to write the quantity and id, and have to have DISTINC and add the Qtd of equal products

  • So adjust your question, because the way you posted it is not very clear.

  • ok Fis the change in question, only now I saw the need for the DISTINC. Could you help me? because I tested here and your select did what I need, all that’s missing is DISTINC and SUM in Qtd.

1

If that’s what I got right, you want to add the column values (Qtd), but not show the same product id.

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

Thus;

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

Browser other questions tagged mysql

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