Select Multiple Columns based on multiple columns from same table in MySql

Question

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;

Answer 1

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);