Nitin Kabra Nitin Kabra - 7 months ago 10
SQL Question

MySQL - SQL LEFT JOIN selects null records not found on other table

I have a following table structure

id item_code type qty

1 1011 Purchase 20
2 1011 Purchase 30
3 1011 Sales -2
4 1011 Purchase 10
5 1011 Sales -7
6 1011 Sales -10
7 1011 Purchase 13
8 1011 Purchase 7


how do i join the table such that i can get data in following pattern

qty_p qty_s

20 -2
30 -7
10 -10
13 null
7 null


motive behind is this to get sale and purchase figures in separate column

Answer

I think you need to use variables here so you can it to join the records.

SELECT  a.qty PurchaseQuantity, 
        b.qty SalesQuantity
FROM
        (
            SELECT  @rank1 := @rank1 + 1 Rank1,
                    a.qty
            FROM    tableName a, (SELECT @rank1 := 0) b
            WHERE   a.type = 'Purchase'
            ORDER   BY a.ID
        ) a
        LEFT JOIN
        (
            SELECT  @rank2 := @rank2 + 1 Rank2,
                    a.qty
            FROM    tableName a, (SELECT @rank2 := 0) b
            WHERE   a.type = 'Sales'
            ORDER   BY a.ID
        ) b ON a.Rank1 = b.Rank2
ORDER   BY a.Rank1