SQL: What is the syntax error in this Create View script? (Beginner)

Question

I am trying to create a new view, with two different queries. I am new to SQL and I'm wondering what is causing the syntax error for the following code:

CREATE VIEW `License_Expiration` AS

/* These queries show the license expiration date for retailers and 
processing facilities, sorted by soonest expiration date */

SELECT `Retailers`.`Retailer_Name`, `Retailers`.`Retail_License_Number`, 
`Retailers`.`Retal_License_Expiration` FROM `mydb2`.`Retailers`
ORDER BY `Retailers`.`Retal_License_Expiration`;

SELECT `Processing Facility`.`Facility_Name`, `Processing 
Facility`.`Facility_License_Num`, `Processing 
Facility`.`Facility_License_Exp` FROM `mydb2`.`Processing Facility`
ORDER BY `Processing Facility`.`Facility_License_Exp`;

The syntax error occurs at the second SELECT statement: "SELECT is not a valid input at this position". I am using SQL Workbench. Any help is appreciated!

EDIT: your solutions have solved it, thanks for your help!!

Answer 1

You cannot create a view with two independent SELECT statements as you have done. According to the MySQL 5.7 documentation, the syntax for a view is as shown below.

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]

Since it appears you want to get license expiration from two types - namely, retailers and facilities - perhaps you could UNION these two SELECT statements and have a CHAR column that indicates if the record is for a retailer or facility. The UNION assumes that all columns are of the same type.

CREATE VIEW `License_Expiration` AS

    /* These queries show the license expiration date for retailers and 
    processing facilities, sorted by soonest expiration date */

    (SELECT `Retailers`.`Retailer_Name`, AS Name,
    `Retailers`.`Retail_License_Number` AS License_Number,
    `Retailers`.`Retal_License_Expiration` AS License_Expiration, 'R'   
    AS Source_Type FROM `mydb2`.`Retailers`)

    UNION

    (SELECT `Processing Facility`.`Facility_Name`, AS Name, 
    `Processing Facility`.`Facility_License_Num` AS License_Number,     
    `Processing Facility`.`Facility_License_Exp` AS License_Expiration,  
    'F' AS Source_Type FROM `mydb2`.`Processing Facility`)

    ORDER BY Name, License Expiration;

Hope this helps. Give it a shot.