Totalize products through two tables - Mysql

Asked

Viewed 37 times

0

I wish the result of SELECT to return:

Product name | Credit | Debit | Balance
Badge 076 |10 | 5 | 5
Badge 064 | 20 | 10 | 10

Table: products [product.id / product.name]
Table: badges [badges.id / id_product ]
Table: credits [id_badge / credit]
Table: debits [id_badge / debit ]

In the SELECT I set only one totalization worked out the other repeated the sum of all the products found in the table.

SELECT produtos.nome, (SELECT SUM(creditos.credito) FROM creditos) AS 'Crédito', (SELECT SUM(debitos.debito) FROM debitos) AS 'Débito' FROM produtos 
INNER JOIN crachas ON crachas.id_produto = produtos.id
INNER JOIN creditos ON creditos.id_cracha = crachas_id
INNER JOIN debitos ON debitos.id_cracha = crachas_id
GROUP BY produtos.id;

Database: Mysql Another thing, there may be NULL values. I appreciate any help.

mysql select group-by inner-join

  • Not said the DBMS , search by PIVOT CASE IIF example around.

  • The database is Mysql

  • https://www.devmedia.com.br/forum/saldo-de-debito-e-credito-no-mesmo-registro-mysql/597158

1 answer

0

Whoa, good night, good night! For your explanation I believe that’s it.

 SELECT produtos.nome
       , SUM(creditos.credito) as 'Crédito'
       , SUM(debitos.debito) as 'Débito' 
FROM produtos 
INNER JOIN crachas 
    ON crachas.id_produto = produtos.id
INNER JOIN creditos 
    ON creditos.id_cracha = crachas_id
INNER JOIN debitos 
    ON debitos.id_cracha = crachas_id
GROUP BY produtos.nome;

My modifications were removing (SELECT SUM... and leaving only SAM for both fields. I also modified the group column by placing the select column.

Browser other questions tagged mysql select group-by inner-join

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