Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Multitable joining in Sql

I am a beginner trying to build very simple inventory system using PHP + SQL
I have 2 warehouse. I added 2 tables, 1 for each warehouse contains (item id)-(in)- (out) like shown below

table1

item id in out
item1 10 0
item1 5 0
item2 0 3
item2 0 2

table2

item id in out
item1 12 0
item1 50 0
item2 0 10
item2 0 30

I have report show balance for each warehouse separately by using query below

Select item_id, sum(in-out) as balance from table1 group by item_id

like below

item id balance
item1 2
item2 20

My question is how show each warehouse balance in one table like below

item id warehouse1 warehouse2
item1 7 2
item2 3 20

I tired with this query but I get wrong results

SELECT table1.item_id, sum(table1.in)-sum(table1.out) as tb1, sum(table2.in)-sum(table2.out) as tb2 FROM table1 , table2 WHERE table1.item_id=table2.item_id GROUP by item_id

the query above have 2 problems

  1. results are not correct
  2. only items in both tables are shown , while any item exist in table and not exists in the second one is not shown
Tags:

2

Answers


  1. 0

    Just calculate the sum (like you did for your first table) for both tables, then join the results together on item_id.

    If you can guarantee that both warehouses will have the exact same unique list of item_ids then you can do an INNER JOIN. I’ve assumed that they may have some discrepancies, so I’ve opted for a FULL JOIN. However, It seems that MySQL doesn’t support the FULL JOIN option. So I’ve taken the UNION of the LEFT JOIN and the RIGHT JOIN to achieve the desired result.

    I’ve quoted the IN and OUT columns with backticks, as IN is a SQL keyword (not sure about OUT, best to be safe). That way they will be treated as column names and not whatever else they mean (i.e. IN is an operator).

    First, setup some tables to mimic your data.

    -- Create table corresponding to warehouse 1
DROP TABLE IF EXISTS warehouse1;
CREATE TABLE warehouse1(item_id CHAR(5) NOT NULL
                    , `in` BIGINT  NOT NULL
                    , `out` BIGINT NOT NULL
                    );
  
INSERT INTO warehouse1(item_id
                    , `in`
                    , `out`
                    )
VALUES('item1', 10, 0)
    , ('item1', 5, 0)
    , ('item2', 0, 3)
    , ('item2', 0, 2);

-- Create table corresponding to warehouse 2
CREATE TABLE warehouse2(item_id CHAR(5) NOT NULL
                    , `in` BIGINT  NOT NULL
                    , `out` BIGINT NOT NULL
                    );
                    
INSERT INTO warehouse2(item_id
                    , `in`
                    , `out`
                    )
VALUES('item1', 12, 0)
    , ('item1', 50, 0)
    , ('item2', 0, 10)
    , ('item2', 0, 30);

    Secondly, perform the aggregations and join the results.

    -- Compute the sums for each table, grouped by the item_id and join them together
-- We want a FULL JOIN here. But MySQL doesn't support it, so we UNION a LEFT and a RIGHT
-- to get the same effect.
SELECT wh1.item_id AS item_id
    , wh1.balance AS warehouse1_balance
    , wh2.balance AS warehouse2_balance
FROM(
    SELECT item_id
        , SUM(`in` - `out`) AS balance
    FROM warehouse1
    GROUP BY item_id
    ) wh1
LEFT JOIN (
    SELECT item_id
        , SUM(`in` - `out`) AS balance
    FROM warehouse2
    GROUP BY item_id
    ) wh2
    ON wh1.item_id = wh2.item_id

UNION

SELECT wh2.item_id AS item_id
    , wh1.balance AS warehouse1_balance
    , wh2.balance AS warehouse2_balance
FROM(
    SELECT item_id
        , SUM(`in` - `out`) AS balance
    FROM warehouse1
    GROUP BY item_id
    ) wh1
RIGHT JOIN (
    SELECT item_id
        , SUM(`in` - `out`) AS balance
    FROM warehouse2
    GROUP BY item_id
    ) wh2
    ON wh1.item_id = wh2.item_id
;

    I have deliberately used UNION instead of UNION ALL as both the LEFT JOIN and the RIGHT JOIN will contain the INNER JOIN. So we don’t want to include the INNER JOIN twice.

    Try it yourself: db<>fiddle

    Login or Signup to reply.
  2. 0

    You have a number of serious design flaws in your inventory system.

    Firstly, you should have only a single table containing both warehouses, with a warehouse_id column.

    Second, this is clearly a table of inventory in and out. It makes no sense to have separate columns, you may as well have a single column, where out is negative.

    warehouse_id item_id qty_moved
    1 item1 10
    1 item1 5
    1 item2 -3
    1 item2 -2
    2 item1 12
    2 item1 50
    2 item2 -10
    2 item2 -30

    Now you can just do a simple query with conditional aggregation

    SELECT
  im.item_id,
  SUM(CASE WHEN warehouse_id = 1 THEN qty_moved END) AS warehouse1,
  SUM(CASE WHEN warehouse_id = 2 THEN qty_moved END) AS warehouse2
FROM InventoryMove im
GROUP BY
  im.item_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search