What type of join to use here?

Question

I have two tables: StorageTransactions and FutureStockUsageMaterials.

The StorageTransactions table shows all the movement of the products in and out of the storage, while the FutureStockUsageMaterials table shows possible future movements of products in and out of the tables.

I've written the following query:

SELECT 
 SUM(StorageTransactions.Quantity) as CurrentStock, 
 COALESCE(FutureStockUsageMaterials.FutureStock, 0) as FutureStock,
 StorageTransactions.products_ProductId 
FROM StorageTransactions 
   LEFT JOIN FutureStockUsageMaterials 
   ON FutureStockUsageMaterials.products_ProductId = StorageTransactions.products_ProductId 
 GROUP BY StorageTransactions.products_ProductId`

For example, if product with the product id of 3 will be used in the future, but there is no record of it being in the older transactions, what I would like to see is a line like this:

CurrentStock  |  FutureStock  | products_ProductId
0             |  -325.00      | 3

This query works as expected, as in showing 3 columns, the first being the current stock of a product, the second being the future stock of the product, and the third being the product itself. My problem is, that when there is no entry for a given product in the StorageTransactions table, but in the future that product should be used, this query does not return that row, I am presuming because of my join.

How could I achieve the desired behaviour, ie. getting all the products that will be used in the future?

Please take a look here: https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join?rq=1 — Michael Hirschler, Feb 11 '19 at 13:20

score 2 · Accepted Answer · answered Feb 11 '19 at 13:20

If StorageTransactions may have null records but FutureStockUsageMaterials will be always available then change LEFT JOIN to RIGHT JOIN.

If both tables may have null records, then you need to use FULL OUTER JOIN but unfortunately FULL OUTER JOIN is not supported in mysql. So we need to apply workaround:

SELECT 
 SUM(StorageTransactions.Quantity) as CurrentStock, 
 COALESCE(FutureStockUsageMaterials.FutureStock, 0) as FutureStock,
 StorageTransactions.products_ProductId 
FROM StorageTransactions 
   LEFT JOIN FutureStockUsageMaterials 
   ON FutureStockUsageMaterials.products_ProductId = StorageTransactions.products_ProductId 
 GROUP BY StorageTransactions.products_ProductId`
UNION 
SELECT 
 SUM(StorageTransactions.Quantity) as CurrentStock, 
 COALESCE(FutureStockUsageMaterials.FutureStock, 0) as FutureStock,
 StorageTransactions.products_ProductId 
FROM StorageTransactions 
   RIGHT JOIN FutureStockUsageMaterials 
   ON FutureStockUsageMaterials.products_ProductId = StorageTransactions.products_ProductId 
 GROUP BY StorageTransactions.products_ProductId`

What type of join to use here?

1 Answers1