Mysql issue in subquery-CodePudding

I have two mysql tables named item and stock.

select * From stock;
 ---------- ---------- ------- --------- ------------------- --------------- 
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id |
 ---------- ---------- ------- --------- ------------------- --------------- 
|       48 | v        | 44.00 |       1 |                 1 |             1 |
|       49 | v        |  8.00 |     263 |                 1 |             1 |
|       50 | a        |  6.00 |       1 |                 1 |             1 |
|       51 | a        |  4.00 |     263 |                 1 |             1 |
|       56 | a        | 21.00 |       1 |                 1 |             1 |
|       57 | a        | 57.00 |     263 |                 1 |             1 |
|       58 | a        |  6.00 |     264 |                 1 |             1 |
|       59 | a        | 19.00 |     301 |                 1 |             1 |
 ---------- ---------- ------- --------- ------------------- ---------------

Now I want to get all the items from item table along with all available qty from stock table.

This is how I tried it. But I coundn't get correct qty from stock table.

SELECT i.item_id
     , i.item_name
     , i.item_code
     , i.sku
     , i.min_qty 
     , i.max_qty   
     , sum(current_stock) as stock
  FROM item i 
      LEFT JOIN (
         SELECT item_id, qty_type, COALESCE(SUM(qty),0) AS current_stock 
           FROM stock 
       GROUP BY item_id
                ) s USING(item_id)
  WHERE s.qty_type = 'a';

Result from above query

 --------- ------------------ ----------- ------------ --------- --------- ------- 
| item_id | item_name        | item_code | sku        | min_qty | max_qty | stock |
 --------- ------------------ ----------- ------------ --------- --------- ------- 
|     264 | HONE CLIP RUBER  | MM-00264  | NOOR-00264 |      10 |      20 | 25.00 |
 --------- ------------------ ----------- ------------ --------- --------- ------- 
1 row in set (0.001 sec)

You can see, I can't get all item records and that stock value also wrong with my qurery.

Can I know what I did wrong in my query?

CodePudding user response：

Move the condition qty_type = 'a' inside the subquery and use COALESCE() in the main query so that you get 0 for items that do not match the join:

SELECT i.*,   
       COALESCE(s.current_stock, 0) AS stock
FROM item i 
LEFT JOIN (
  SELECT item_id, SUM(qty) AS current_stock 
  FROM stock
  WHERE qty_type = 'a' 
  GROUP BY item_id
) s USING(item_id);

The aggregation is needed only in the subquery.

CodePudding user response：

Please try this:

SELECT i.item_id
     , i.item_name
     , i.item_code
     , i.sku
     , i.min_qty 
     , i.max_qty   
     , current_stock as stock
FROM item i 
LEFT JOIN
    (
        SELECT item_id, SUM(qty) AS current_stock 
        FROM stock 
        WHERE qty_type = 'a'
        GROUP BY item_id
                
    ) s USING(item_id);