how to write mysql query for the following problem?



  • products table table1 which has item names

    item_cid item_id item_name
    8 403 Tequila1
    8 404 Tequila2
    8 405 Tequila3
    8 406 Tequila4
    8 407 Tequila5
    8 408 Tequila6
    2 409 budwiser1
    2 5 budwiser2
    2 7 budwiser4
    2 8 budwiser5

    table2 which has a current stock

    item_cid item_id item_name current stock
    8 403 Tequila1 11
    8 404 Tequila2 10
    8 405 Tequila3 32
    8 406 Tequila4 44
    2 409 budwiser1 55
    2 5 budwiser2 58

    table3 which has purchase stock

    item_cid item_id item_name purchase qty
    8 407 Tequila5 4
    8 408 Tequila6 7
    2 7 budwiser4 8
    2 8 budwiser5 9
    2 409 budwiser1 5
    2 5 budwiser2 2

    the result I want

    item_cid item_id item_name current stock purchase qty total
    8 403 Tequila1 11 null 11
    8 404 Tequila2 10 null 10
    8 405 Tequila3 32 null 32
    8 406 Tequila4 44 null 44
    8 407 Tequila5 null 4 4
    8 408 Tequila6 null 7 7
    2 409 budwiser1 55 5 60
    2 5 budwiser2 58 2 60
    2 7 budwiser4 null 8 8
    2 8 budwiser5 null 9 9

    the query I tried is not able to retrieve rows from table 3 in the final result this what I am getting

    item_cid item_id item_name current stock purchase qty total
    8 403 Tequila1 11 null 11
    8 404 Tequila2 10 null 10
    8 405 Tequila3 32 null 32
    8 406 Tequila4 44 null 44
    8 407 Tequila5 null 4 4
    8 408 Tequila6 null 7 7
    2 409 budwiser1 55 5 60
    2 5 budwiser2 58 2 60
    SELECT 
       *
    

    FROM
    table2 a
    LEFT JOIN
    table3 b ON a.item_id = b.item_id
    JOIN
    table1 d ON d.item_id=a.item_id
    ORDER BY a.item_id



  • SELECT item_cid, 
           item_id, 
           item_name,
           SUM(table2.current_stock) current_stock,
           SUM(table3.purchase_qty) purchase_qty,
           COALESCE(SUM(table2.current_stock), 0) + COALESCE(SUM(table3.purchase_qty), 0) total
    FROM table1
    LEFT JOIN table2 USING (item_cid, item_id, item_name)
    LEFT JOIN table3 USING (item_cid, item_id, item_name)
    GROUP BY item_cid, item_id, item_name
    

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=aeac4b2165c8b8370fbbc082f08b3145

    PS. Your tables are not normalized.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2