Multitable joining in Sql

Question

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

Answer 1

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

Answer 2

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;