Using CREATE VIEW with multiple tables

user_8057648 0
Viewed 6079

I'm trying to use the CREATE VIEW along with multiple cells from 3 different tables with multiplication between two cells. There is a syntax error somewhere with this somewhere but I can't seem to identify it.

CREATE VIEW ORDERS AS CustFirstname, CustLastName, o.Ordernumber AS OrderNumber, 
o. OrderDate AS OrderDate, o.ShipDate AS ShipDate, c.QuotedPrice, 
c.QuantityOrdered, c.QuotedPrice * c.QuantityOrdered AS ItemTotal FROM Customers 
NATURAL JOIN Orders o NATURAL JOIN Order_Details c

The output i'm trying to get is |CustFirstname|CustLastName|OrderNumber|OrderDate|ShipDate|ItemTotal|

Update: If I replace "CREATE VIEW ORDERS AS" with "SELECT" it seems to work

mysql
2 Answers

On your CREATE VIEW statement you are missing the SELECT of the query:

CREATE VIEW ORDERS AS 
    SELECT CustFirstname, CustLastName, o.Ordernumber AS OrderNumber, 
        o. OrderDate AS OrderDate, o.ShipDate AS ShipDate, c.QuotedPrice, 
        c.QuantityOrdered, c.QuotedPrice * c.QuantityOrdered AS ItemTotal 
    FROM Customers NATURAL JOIN Orders o NATURAL JOIN Order_Details c

On the definition of CREATE VIEW you can see a select_statement is required:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

You can find more information about CREATE VIEW on the official docs.

user_38408400

You are missing the most important statement which is "SELECT".

CREATE VIEW ORDERS AS 
SELECT CustFirstname, CustLastName, o.Ordernumber AS OrderNumber, 
o. OrderDate AS OrderDate, o.ShipDate AS ShipDate, c.QuotedPrice, 
c.QuantityOrdered, c.QuotedPrice * c.QuantityOrdered AS ItemTotal FROM Customers 
NATURAL JOIN Orders o NATURAL JOIN Order_Details c
user_12605000
Related