0

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

Ankush
  • 6,767
  • 8
  • 30
  • 42

1 Answers1

0

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

Poul Kruijt
  • 69,713
  • 12
  • 145
  • 149
exussum
  • 18,275
  • 8
  • 32
  • 65
  • Why does it make no sense? `(SELECT * FROM players WHERE lid = 0)` gives me a list of all the players and `(SELECT * FROM players WHERE lid = 1)` gives me a list of players with certain attributes. `WHERE NOT EXISTS` then gives me players who don't have all the attributes. Do you think this is a wrong way to implement it? – Ankush Apr 18 '16 at 06:52
  • 1
    It doesn't make sense because if you select from players where `lid = 0` then it already rules out players where `lid = 1`. It's redundant and probably inefficient. – Rob Apr 18 '16 at 07:01
  • oh. `lid = 0` is a list of all the players. It contains all unique players. `lid = 1` is basically a list that contains some of the players from `0` but have lid set to `1` – Ankush Apr 18 '16 at 07:08