CODEWITHSUNDEEP
mysqlleft-join
cyberfly
cyberfly

Reputation: 5868

Left join in complicated sql query

Hi guys i have this query

SELECT currency_code, SUM(
CASE
WHEN TYPE = 'cash_in'
THEN amount
END ) AS cash_in, SUM(
CASE
WHEN TYPE = 'cash_out'
THEN amount
END ) AS cash_out, SUM(
CASE
WHEN TYPE = 'cash_in'
THEN amount
ELSE - amount
END ) AS balance
FROM tb_cash_transaction
LEFT JOIN tb_currency ON tb_currency.CURRENCY_ID = tb_cash_transaction.CURRENCY_ID
WHERE TYPE IN (
'cash_in', 'cash_out'
)
GROUP BY currency_code

That will output this:

alt text

The problem is the query dont display another currency_code KRW which dont have data in tb_cash_transaction. Example in tb_currency

alt text

How to solve this?

Upvotes: 0

Views: 222

Answers (1)

John Petrak
John Petrak

Reputation: 2928

Try this

SELECT currency_code, SUM(
CASE
WHEN TYPE = 'cash_in'
THEN amount
END ) AS cash_in, SUM(
CASE
WHEN TYPE = 'cash_out'
THEN amount
END ) AS cash_out, SUM(
CASE
WHEN TYPE = 'cash_in'
THEN amount
ELSE - amount
END ) AS balance
FROM tb_currency 
LEFT JOIN tb_cash_transaction ON tb_currency.CURRENCY_ID = tb_cash_transaction.CURRENCY_ID AND tb_cash_transaction.TYPE IN (
'cash_in', 'cash_out'
)
GROUP BY currency_code

Upvotes: 1

Related Questions