I want to return 3 columns Dtls (Details), Purchase_amount as credit, Sale_amount as debit
But When Purchase_id = 3, Purchase_amount must be selected and Sale_amount must be 0, and vice-versa.
The required result:
The required result against the number 3:
If I try
SELECT *
FROM (
select c.Sale_amount as debit
from yarn c
where c.Sale_id=3
) A,
(
select c2.Purchase_amount as credit
from yarn c2
where c2.Purchase_id=3
) B;
it provides queer results.
This does not work:
SELECT A as debit, B as credit
FROM (
select c.Sale_amount as debit
from yarn c
where c.Sale_id=3
) A,
(
select c2.Purchase_amount as credit
from yarn c2
where c2.Purchase_id=3
) B;
Filter the table to get only the rows with Purchase_id
or Sale_id
equal to 3 and use CASE
expressions to get the columns Credit
and Debit
:
SELECT CASE WHEN Purchase_id = 3 THEN Purchase_amount ELSE 0 END AS Credit,
CASE WHEN Sale_id = 3 THEN Sale_amount ELSE 0 END AS Debit
FROM yarn
WHERE 3 IN (Purchase_id, Sale_id);