MySQL Stored Procedure with Multiple Tables

Question

I want to show all the records of my items table. But some quantity need to be a negative value.

BEGIN 
    SET @ItemId1 = (SELECT ItemId FROM items);

    SELECT 
        SUM(ROUND(Quantity)) 
    FROM (
        SELECT
            COALESCE(SUM(od.Quantity),0) as Quantity
        FROM sts as T1
        LEFT JOIN orderset_details as od 
            ON T1.OrdersetId = od.OrdersetId
        WHERE od.ItemId = @ItemId1
        UNION ALL
        SELECT 
            COALESCE(SUM(od.Quantity),0) as Quantity
        FROM drs as T2
        LEFT JOIN orderset_details as od 
            ON T2.OrdersetId = od.OrdersetId
        WHERE od.ItemId = @ItemId1
        UNION ALL  
        SELECT 
            COALESCE(
                SUM(
                    IF(r.AddsToStock = '1', od.Quantity, 0 - od.Quantity)
                ),0
            ) as Quantity
        FROM stockadjustment as T3
        LEFT JOIN reason as r 
            ON r.ReasonId = T3.ReasonId
        LEFT JOIN orderset_details as od 
            ON T3.OrdersetId = od.OrdersetId 
        WHERE od.ItemId = @ItemId1
        UNION ALL
        SELECT 
            COALESCE(
                SUM(
                    IF(T4.OrdersetId = od.OrdersetId, 0 - od.Quantity, -1 * od.Quantity)
                ),0
            ) as Quantity
        FROM issueslip as T4
        LEFT JOIN orderset_details as od 
            ON T4.OrdersetId = od.OrdersetId 
        WHERE od.ItemId = @ItemId1
        UNION ALL
        SELECT 
            COALESCE(
                SUM(
                    IF(T4.OrdersetId = od.OrdersetId, 0 - od.Quantity, -1 * od.Quantity)
                ),0
            ) as Quantity
        FROM invoice as T4
        LEFT JOIN orderset_details as od 
            ON T4.OrdersetId = od.OrdersetId
        WHERE od.ItemId = @ItemId1
    ) as t;
END

This code got an error MySQL said: #1242 -Subquery returns more than 1 row.

I want all the quantity of every item to be shown. Thats why I have @ItemId1 to get all the ItemId from items. How Can I do that?

Yes, but you're trying to assign multiple values to a scalar. — Marko Juvančič
– Marko Juvančič, Commented Jul 28, 2016 at 14:24

Marko Juvančič · Accepted Answer · 2016-07-28 14:30:40Z

1

You should group by ItemId. Try this

SELECT 
    ItemId, SUM(ROUND(Quantity)) 
FROM (
    SELECT
        od.ItemId, COALESCE(SUM(od.Quantity),0) as Quantity
    FROM sts as T1
    LEFT JOIN orderset_details as od 
        ON T1.OrdersetId = od.OrdersetId
   -- WHERE od.ItemId = @ItemId1
    GROUP BY od.ItemId
    UNION ALL
    SELECT 
        od.ItemId, COALESCE(SUM(od.Quantity),0) as Quantity
    FROM drs as T2
    LEFT JOIN orderset_details as od 
        ON T2.OrdersetId = od.OrdersetId
    --WHERE od.ItemId = @ItemId1
    GROUP BY od.ItemId
    UNION ALL  
    SELECT 
      od.ItemId,
        COALESCE(
            SUM(
                IF(r.AddsToStock = '1', od.Quantity, 0 - od.Quantity)
            ),0
        ) as Quantity
    FROM stockadjustment as T3
    LEFT JOIN reason as r 
        ON r.ReasonId = T3.ReasonId
    LEFT JOIN orderset_details as od 
        ON T3.OrdersetId = od.OrdersetId 
    --WHERE od.ItemId = @ItemId1
    GROUP BY od.ItemId
    UNION ALL
    SELECT 
       od.ItemId,
        COALESCE(
            SUM(
                IF(T4.OrdersetId = od.OrdersetId, 0 - od.Quantity, -1 * od.Quantity)
            ),0
        ) as Quantity
    FROM issueslip as T4
    LEFT JOIN orderset_details as od 
        ON T4.OrdersetId = od.OrdersetId 
    --WHERE od.ItemId = @ItemId1
    GROUP BY od.ItemId
    UNION ALL
    SELECT 
        od.ItemId
        COALESCE(
            SUM(
                IF(T4.OrdersetId = od.OrdersetId, 0 - od.Quantity, -1 * od.Quantity)
            ),0
        ) as Quantity
    FROM invoice as T4
    LEFT JOIN orderset_details as od 
        ON T4.OrdersetId = od.OrdersetId
    --WHERE od.ItemId = @ItemId1
    GROUP BY od.ItemId
) as t
GROUP BY ItemId;

answered Jul 28, 2016 at 14:30

Marko Juvančič

5,9201 gold badge27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Meyka Jograt Over a year ago

it worked but ... Showing rows 0 - 29 ( 2,890 total, Query took 137.2460 sec) is there any other way?

Marko Juvančič Over a year ago

Check the explain plan. Are there good indexes on your tables?

Meyka Jograt Over a year ago

and btw from what you did GROUP BY ItemId make it work but the ItemId should be coming from the items tables

Marko Juvančič Over a year ago

Start here: programmers.stackexchange.com/questions/78555/…

Collectives™ on Stack Overflow

MySQL Stored Procedure with Multiple Tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related