CODEWITHSUNDEEP
mysql
Joshua Nguyen
Joshua Nguyen

Reputation: 153

Using CREATE VIEW with multiple tables

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

Upvotes: 2

Views: 6650

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43564

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.

Upvotes: 4

Lamar
Lamar

Reputation: 1839

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

Upvotes: 3

Related Questions