sqlSubqeryInSelect.sql

SELECT
    s.date,
    s.project_id,
    pm.property_name,
    s.stock_type,
    s.category_id,
    c.title,
    s.sub_category_id,
    sc.sub_category_name,
    s.item_id,
    i.item_name,
    (CASE WHEN s.stock_type = 1 THEN s.quantity ELSE 0 END) AS purchase_qty,
    s.unit_id,
    u.name,
    (CASE WHEN s.stock_type = 2 THEN s.quantity ELSE 0 END) AS issue_qty,
    s.unit_id,
    u.name
FROM
    stock AS s
LEFT JOIN property_master AS pm
ON
    s.project_id = pm.id
LEFT JOIN category AS c
ON
    s.category_id = c.id
LEFT JOIN sub_category AS sc
ON
    s.sub_category_id = sc.id
LEFT JOIN item AS i
ON
    s.item_id = i.id
LEFT JOIN unit AS u
ON
    s.unit_id = u.id
ORDER BY
    s.date,
    s.project_id,
    s.category_id,
    s.sub_category_id,
    s.item_id