What type of join to use here?

62
February 12, 2019, at 10:20 AM

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?

Answer 1

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`
READ ALSO
How to check if directory is correct for uploading files in php?

How to check if directory is correct for uploading files in php?

I am making an application form for users to apply for jobs and upload their CVsThe php code I made sends the file name to the DB so it is working correctly I guess

59
Creating a Pivot (crosstab) query for a golf league [on hold]

Creating a Pivot (crosstab) query for a golf league [on hold]

I’m trying to create a pivot query (Cross Tab)for a golf league with about 20 playersIn my database I place the playerID, Name, Date, Round#(1,2,3, etc) and score

49
Access denied for user root@localhost on Ubuntu and OS X

Access denied for user root@localhost on Ubuntu and OS X

I'm developing a web application and it is working just fine in Windows, but now I'm trying to run it in Ubuntu and in a Macbook and it's giving me a Access denied error

43
Question marks returned instead of Arabic characters ONLY when xampp servers are launched from within program

Question marks returned instead of Arabic characters ONLY when xampp servers are launched from within program

As the title says, question marks are returned only when I run mysqldexe and httpd

62