Search code examples
mysqlsqlview

#1064 - You have an error in your SQL syntax when creating a View

What's wrong with this code that's making it spit back an error?

My code is as follows:

CREATE OR REPLACE VIEW vw_training AS 
SELECT training.train_attended, clients.client_firstname, clients.client_lastname, clients.client_swn, clients.client_id, locations.loc_id, locations.loc_title, locationsp.loc_id, locationsp.loc_title, 
FROM training 
JOIN clients ON clients.client_id = training.train_clientid
JOIN locations AS locationsp ON locations.loc_id = training.train_pickup
LEFT JOIN locations ON locations.loc_id = clients.client_winz

And this is the error I'm getting back:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM training JOIN clients ON clients.client_id = training.train_clientid JOIN' at line 3

I'm running phpMyAdmin version 3.5.2.2.

I've used this script with different values before with no issues.

Solution

  • You have an extra trailing comma before the FROM clause

    SELECT ....,
       locationsp.loc_id, 
       locationsp.loc_title, -- <<== remove this trailing comma
FROM   training ...

    and another error that will raise this message: Unknown column 'locations.loc_id' in 'on clause' is the use of tablename and not the alias supplied. it should be,

    JOIN locations AS locationsp ON locationsp.loc_id = training.train_pickup
                                    ^^ should use alias here