sql filter in another table

Asked

Viewed 90 times

0

Good morning,

I have a problem where I have the following code working.

select 
(sum((case when (lanc.id_tipo = 1 OR lanc.id_tipo = 5) 
then lanc.valor else 0 end)) + sum((case when (lanc.id_tipo = 2) 
then lanc.valor else 0 end))) AS Saldo
from lancamentos lanc
where lanc.id_situacao = 1 or lanc.id_situacao = 3 or lanc.id_situacao = 5
group by id_contas

I need to insert a filter, only it’s in another bank table, I have the accounts table and a column called id_tipo_conta I would need him to add up only type 1, and I can’t get that filter into that code.

Someone can help me?

sql phpmyadmin

  • Is there supposed to be some kind of connection between these two tables (lancings and accounts), perhaps a foreign key? If it exists just make an INNER JOIN.

  • 1

    Add a left Join with the account table, then you can filter by account type in Where clause.

  • Yes you do have the account Cod as foreign key...

2 answers

0

I managed to do so,

select lanc.id_contas, sum(case when (lanc.id_type = 1 OR lanc.id_type = 5) then lanc.value Else 0 end)) AS Revenue, sum(case when (lanc.id_type = 2) then lanc.value Else 0 end)) AS Expenses, (sum((case when (lanc.id_type = 1 OR lanc.id_type = 5) then lanc.value Else 0 end)) + sum((case when (lanc.id_type = 2) then lanc.value Else 0 end))) AS Balance from lancings lanc LEFT JOIN accounts AS f ON f.id_tipo_account = 1 Where lanc.id_situacao = 1 or lanc.id_situacao = 3 or lanc.id_situacao = 5 group by lanc.id_contas

Only that the data got the wrong data because the "Where lanc.id_situation = 1 or lanc.id_situation = 3 or lanc.id_situation = 5" was not right, I cannot make the Where correct with the LEFT JOIN accounts AS f ON f.id_type account = 1

0

I managed to solve with this code.

select lanc.id_contas,
(sum((case when (lanc.id_tipo = 1 OR lanc.id_tipo = 5 or lanc.id_tipo = 4) 
then lanc.valor else 0 end)) + sum((case when (lanc.id_tipo = 2 or lanc.id_tipo = 3) 
then lanc.valor else 0 end))) AS Saldo
from lancamentos lanc
LEFT join contas as f on lanc.id_contas = f.id_contas
where f.id_tipo_conta = 1 AND lanc.id_situacao = 1 or lanc.id_situacao = 3
group by lanc.id_contas

Browser other questions tagged sql phpmyadmin

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