What is the error Every derived table must have its own alias in MySQL

Question

I am running this query on MySQL  SELECT ID FROM       SELECT ID  msisdn     FROM           SELECT   FROM TT2            and it is giving this error      Every derived table must have its own alias     What s causing this error

User · Accepted Answer

Every derived table  AKA sub-query  must indeed have an alias  I e  each query in brackets must be given an alias  AS whatever   which can the be used to refer to it in the rest of the outer query   SELECT ID FROM       SELECT ID  msisdn FROM           SELECT   FROM TT2       AS T   AS T   In your case  of course  the entire query could be replaced with   SELECT ID FROM TT2

User · Answer

Here s a different example that can t be rewritten without aliases   can t GROUP BY DISTINCT    Imagine a table called purchases that records purchases made by customers at stores  i e  it s a many to many table and the software needs to know which customers have made purchases at more than one store   SELECT DISTINCT customer id  SUM 1    FROM   SELECT DISTINCT customer id  store id FROM purchases    GROUP BY customer id HAVING 1  lt  SUM 1       will break with the error Every derived table must have its own alias   To fix   SELECT DISTINCT customer id  SUM 1    FROM   SELECT DISTINCT customer id  store id FROM purchases  AS custom   GROUP BY customer id HAVING 1  lt  SUM 1       Note the AS custom alias

User · Answer

I think it s asking you to do this    SELECT ID FROM  SELECT ID               msisdn        FROM  SELECT   FROM TT2  as myalias        as anotheralias    But why would you write this query in the first place

User · Answer

I arrived here because I thought I should check in SO if there are adequate answers  after a syntax error that gave me this error  or if I could possibly post an answer myself  OK  the answers here explain what this error is  so not much more to say  but nevertheless I will give my 2 cents using my words  This error is caused by the fact that you basically generate a new table with your subquery for the FROM command  That s what a derived table is  and as such  it needs to have an alias  actually a name reference to it   So given the following hypothetical query  SELECT id  key1 FROM       SELECT t1 ID id  t2 key1 key1  t2 key2 key2  t2 key3 key3     FROM table1 t1      LEFT JOIN table2 t2 ON t1 id   t2 id     WHERE t2 key3    some-value    AS tt  So  at the end  the whole subquery inside the FROM command will produce the  table that is aliased as tt and it will have the following columns id  key1  key2  key3  So  then with the initial SELECT from that table we finally select the id and key1 from the tt

[mysql] What is the error "Every derived table must have its own alias" in MySQL?

Examples related to mysql

Examples related to subquery

Examples related to mysql-dependent-subquery

Examples related to mysql-error-1248