MySQL select query for stock management

1
votes

I'm trying to get current stock from purchase and sale table but getting only one result.

Here is the query I wrote.

SELECT `I`.`id`, `I`.`modal`, `I`.`brand`, 
IFNULL(SUM(P.qty), 0) as p_qty, 
IFNULL(SUM(S.qty), 0) as s_qty, 
SUM(P.qty)-SUM(S.qty) as t_qty 
FROM `items` `I` 
    LEFT JOIN `purchase_details` `P` ON `P`.`item_id` = `I`.`id` 
    LEFT JOIN `sale_details` `S` ON `S`.`item_id` = `I`.`id` 
WHERE `I`.`id`
IN("1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70") 
GROUP BY `P`.`item_id`

Expected result : All the items should come with or without zero according to stock.

2
phpmysqldatabase
Is the 'IN' statement you added there necessary? - jarodsmk
Yes, because Items are not limited and predictable. if you have other way to do this, suggest me. - chigs
I'm just thinking - it's ok if there's always 70 items in your table, but if you add items to your stock, you'd need to add the ID to that statement for it to be included in the results. If not, you could omit the WHERE...IN... statement and it should work as expected too :) - jarodsmk
Out of curiosity, which item (ID) does it return? - jarodsmk
yes I know, these ids are coming dynamically. from one other query result. - chigs

2 Answers

1
votes

Try GROUP BY I.id

    SELECT
    `I`.`id`,
    `I`.`modal`,
    `I`.`brand`,
    IFNULL(SUM(P.qty),
    0) AS p_qty,
    IFNULL(SUM(S.qty),
    0) AS s_qty,
    SUM(P.qty) - SUM(S.qty) AS t_qty
FROM
    `items` `I`
LEFT JOIN
    `purchase_details` `P`
ON
    `P`.`item_id` = `I`.`id`
LEFT JOIN
    `sale_details` `S`
ON
    `S`.`item_id` = `I`.`id`
WHERE
    `I`.`id` IN(
        "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70"
    )
GROUP BY
    `I`.`id`
0
votes

Try this 

SELECT
    `I`.`id`,
    `I`.`modal`,
    `I`.`brand`,
    IFNULL((SELECT SUM(P.qty) FROM purchase_details `P` WHERE p.item_id = I.id),
    0) AS p_qty,
    IFNULL(SUM(S.qty),
    0) AS s_qty
FROM `items` `I`
LEFT JOIN `sale_details` `S` ON `S`.`item_id` = `I`.`id`
WHERE  `I`.`id`  BETWEEN 1 AND 70
GROUP BY I.id