Left Join in MySQL View

Question

I have two tables, one tracks purchases of products (indicated by its EAN), the other sales of the products. But not all products sold are in the purchase table and vice versa. So for example:

Purchase

PurchaseNo	EAN
1	0001
2	0002
3	0003
4	0004

Sale

SaleNo	EAN
1	0002
2	0003
3	0004
4	0005

I also have a table with the product specifications for the EAN numbers:

ProductEAN

EAN	Name
0001	Product1
0002	Product2
0003	Product3
0004	Product4
0005	Product5

I now want to create a view that tells me my current Inventory (even if the stock is negative). My current approach is to create one view for purchase and sale each where a column "Amount" tells me how many products I have bought or sold.

So for my purchases that would be:

CREATE VIEW `PurchaseAmount` AS
SELECT 
`ProductEAN`.`EAN` AS `EAN`,
COUNT(`Purchase`.`EAN`) AS `Amount`
FROM (`ProductEAN` JOIN `Purchase`)
WHERE `ProductEAN`.`EAN` = `Purchase`.`EAN`
GROUP BY `ProductEAN`.`EAN`

And the equivalent for my sales.

I now want to combine these two views to create my final inventory view. The problem is that as far as I know and tested, I can only do normal joins in my Views which results in my two "sub"-views not containing the EANs that weren't purchased or sold. And consequently my final inventory view can only show the EANs that are in both the purchase and sale table.

Is there any way to do a left join in a MySQL View to just keep all EAN even if they are not used in the table the view references.

Answer 1

You can do it by first computing the COUNT over purchases and sales separately, then leaving the LEFT JOIN as the last operations to make the query more efficient.

SELECT ProductEAN.EAN                           AS EAN,
       COALESCE(Purchases.num_purchases, 0)     AS num_purchases,
       COALESCE(Sales.num_sales, 0)             AS num_sales
FROM      ProductEAN
LEFT JOIN (SELECT EAN,
                  COUNT(EAN) AS num_purchases
           FROM Purchase
           GROUP BY EAN                      ) Purchases
       ON ProductEAN.EAN = Purchases.EAN
LEFT JOIN (SELECT EAN,
                  COUNT(EAN) AS num_sales
           FROM Sale
           GROUP BY EAN                      ) Sales
       ON ProductEAN.EAN = Sales.EAN

Hence you can take this code and store it inside a view.

Check the demo here.