Create a view from Select statement with multiple subqueries / joins

join, mysql, sql, subquery, view

Solution

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

One of the restrictions is that `VIEW`s 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

Problem

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 ```

Original source