Ankush Ankush - 7 months ago 44
SQL Question

Every derived table must have its own alias - error when using multiple SELECT statements

I'm trying to use multiple SELECT statements in a query to get data from the database but I get an error. The query is:

SELECT *
FROM (SELECT *
FROM players
WHERE lid = 0)
WHERE NOT EXISTS (SELECT *
FROM players
WHERE lid = 1)


The error is get is:


Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1248 Every derived table must have its own alias'


I tried changing my query to

SELECT *
FROM (SELECT *
FROM players
WHERE lid = 0)
WHERE NOT EXISTS (SELECT *
FROM players
WHERE lid = 1) AS T


but got the same error again. I can't understand which part of the query needs an alias and why

Answer

The from table needs an alias

SELECT * 
        FROM (SELECT * 
              FROM players 
              WHERE lid = 0) as T
        WHERE NOT EXISTS (SELECT * 
                          FROM players 
                          WHERE lid = 1)

Will work

Though the query seems to make no sense. It is not a syntax error anymore

Comments