Left Join in MySQL View-CodePudding

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.

CodePudding user response：

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.

Refer to this fiddle for testing: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b2d777f367960cf90d6539980c2ad935.