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?
This question is related to
mysql
subquery
mysql-dependent-subquery
mysql-error-1248
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
.
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 < 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 < SUM(1);
( Note the AS custom
alias).
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?
Source: Stackoverflow.com