Create a view from Select statement with multiple subqueries / joins

Question

So I have multiple tables and I'm trying to create a view to show everything as returned in my select statement. However it seems that mysql does not allow to create Views which have subqueries in from.

So not really sure how to convert this to a view.

Select  Title as "AlbumName" , 
        Sum(trk1.Price * trk1.Quant) as "TotalSales"
From    Album alb
        INNER JOIN
        (
            SELECT  AlbumId, 
                    t1.UnitPrice as "Price" , 
                    t1.Quantity as "Quant"
            FROM    Track trk
                    INNER JOIN
                    (
                        SELECT  TrackId, UnitPrice, Quantity
                        FROM    InvoiceLine
                        WHERE   InvoiceId IN ( SELECT InvoiceId FROM Invoice )
                    ) AS t1 ON (trk.TrackId = t1.TrackId)
        ) as trk1 ON (alb.AlbumId = trk1.AlbumId)
Group By alb.AlbumId

Solution 1

Creating a VIEW in MySQL is plain easy but there are some restrictions. See HERE: MySQL View

One of the restrictions is that VIEWs cannot have SELECT statement that contain a subquery in the FROM clause. So as an alternative, create a view for the subquery first which contains IN clause.

CREATE VIEW InvoiceLineView
AS
SELECT  DISTINCT a.*
FROM    InvoiceLine a
        INNER JOIN Invoice b
            ON a.InvoiceId = b.InvoiceId;

Once the view was created, you can now proceed by joining the view from your original query so you can now create a fully working VIEW. You won't need extra view for uncalculated subqueries.

CREATE VIEW AlbumSales
AS
Select  Title as AlbumName, 
        Sum(t1.UnitPrice * t1.Quantity) as TotalSales
From    Album alb
        INNER JOIN Track trk
            ON alb.AlbumId = trk1.AlbumId
        INNER JOIN InvoiceLineView t1
            ON trk.TrackId = t1.TrackId
Group   By alb.AlbumId;

---

Though, VIEWS in MySQL are horrible.

• MySQL VIEW as performance troublemaker

Solution 2

If you really need this as a view, you have to wrap subqueries as views first.

CREATE VIEW vw_sub1 AS
SELECT TrackId, UnitPrice, Quantity
  FROM InvoiceLine
 WHERE InvoiceId
    IN (SELECT InvoiceId FROM Invoice);

CREATE VIEW vw_sub2 AS
SELECT AlbumId, t1.UnitPrice Price, t1.Quantity Quant
  FROM Track trk INNER JOIN 
       vw_sub1 t1 ON trk.TrackId = t1.TrackId;

CREATE VIEW vw_main AS
SELECT Title AlbumName, Sum(trk1.Price * trk1.Quant) TotalSales
  FROM Album alb INNER JOIN
       vw_sub2 trk1 ON alb.AlbumId = trk1.AlbumId
 GROUP BY alb.AlbumId;

Code is not tested