A single SQL query for a user’s products!

Asked

Viewed 78 times

2

I have the following appointment:

SELECT id_produto, produto, (SUM(credito) - SUM(debito)) as producao_total 
FROM produtos_farm PF 
INNER JOIN produtos P ON P.id = PF.id_produto 
WHERE PF.id_usuario = 20 AND PF.id_produto = 1

This returns me the sum, product name and product id.

A user can have up to 10 products.

How would I not need to make 10 queries (one for each product)?

Tabela PRODUTOS:
id,
nome,
producao,
preco_padrao

Tabela PRODUTOS_FARM:
id,
id_produto,
id_usuario,
credito,
debito
mysql

  • You can’t add a clause IN of the products to be researched? With this, you would avoid n consultations.

  • I tried, but returns the same result as the query @rLinhares wrote.

2 answers

5


Assuming id_produto identifies each product only try the following:

SELECT id_produto, produto, (SUM(credito) - SUM(debito)) as producao_total 
FROM produtos_farm PF 
INNER JOIN produtos P ON P.id = PF.id_produto 
WHERE PF.id_usuario = 20 AND id_produto BETWEEN 1 AND 10
GROUP BY id_produto

In this case the total production of the user 20 will be broken down by product. If there are only 10 products in the database and you want to return all of them to clause AND id_produto BETWEEN 1 AND 10 is not necessary (as per the answer of @rhubarb).

  • Perfect!! And thanks for the explanation too!!

4

Just remove the id_produto of where, so you will bring all user products consulted:

SELECT id_produto, produto, (SUM(credito) - SUM(debito)) as producao_total 
FROM produtos_farm PF 
INNER JOIN produtos P ON P.id = PF.id_produto 
WHERE PF.id_usuario = 20
GROUP BY id_produto
  • 1

    +1 because that is the same answer as mine.

  • It gives the result of total production = 17 (it joins all user products). I want to separate the total product for each product (1 to 10).

  • 2

    Puts the product in the SELECT and makes a GROUP BY column. It would not solve?

  • I don’t get it @Lucasdesouzacruz

  • 4

    At the end of SQL, you put GROUP BY ID_PRODUTO, PRODUTO. This would categorize summations by product

  • 2

    Thanks @Lucasdesouzacruz! It worked!

Show 1 more comment

Browser other questions tagged mysql

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