In this article, we will be discussing the below points
- HOW TO FIX THE ERROR in MySQL : Every derived table must have its own alias
- Every derived table must have its own alias : JOIN
Let’s get started, but first, we will look into the data we will be using in our examples. Assume that we have a table sale_details with the below rows.
HOW TO FIX THE ERROR in MySQL : Every derived table must have its own alias
What is a derived table? Well, the Derived table is an expression that creates a temporary table in the FROM clause’s scope in the query. It is a subquery in the SELECT statement and FROM clause. Let’s look into an example to get the average of the maximum no_products_sold for each salesperson for a department. Observe the below query.
SELECT CEIL(AVG(max_sales)) FROM (SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name);
In the above query, we created a derived table from subquery “SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name,” which is within the scope of the FROM clause.
On running the query written above to get the average of the maximum no_products_sold for each salesperson per department, we get an ERROR :
Frequently Asked:
Action Output Message: SELECT CEIL(AVG(max_sales)) FROM (SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name) LIMIT 0, 1000 Error Code: 1248. Every derived table must have its own alias 0.00025 sec
It is highlighting that an Error:1248 Every derived table must have its alias has occurred. The reason for the same is that we need to provide an alias for the sub-queries while working in MySQL (alias is just a temporary/false name). Observe the corrected query below:
SELECT CEIL(AVG(max_sales)) AS average_of_max_sales_per_salesman FROM (SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name) AS sales_alias;
In the above query, we have added an alias sales_alias for the derived table. On running the query now, we get the desired result.
Action Output Message: SELECT CEIL(AVG(max_sales)) FROM (SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name)as sales_alias LIMIT 0, 1000 1 row(s) returned 0.00086 sec / 0.000018 sec.
Output :-
Every derived table must have its own alias : JOIN
Let’s see how to work with joins while using the derived tables. We will be using the same table sale_details to get all the columns for a salesperson corresponding to the row, which shows the maximum sale for a product department wise. We need to do an inner join of the sales_details table with a derived table for the desired result. Observe the below query, it’s output, and explanation.
SELECT sd1.* FROM sale_details sd1 INNER JOIN (SELECT sale_person_name, MAX(no_products_sold) AS MaxSale FROM sale_details GROUP BY sale_person_name) sd2 ON sd1.sale_person_name = sd2.sale_person_name AND sd1.no_products_sold = sd2.MaxSale;
Action Output Message: SELECT sd1.* FROM sale_details sd1 INNER JOIN (SELECT sale_person_name, MAX(no_products_sold) AS MaxSale FROM sale_details GROUP BY sale_person_name) sd2 ON sd1.sale_person_name = sd2.sale_person_name AND sd1.no_products_sold = sd2.MaxSale LIMIT 0, 1000 4 row(s) returned 0.00097 sec / 0.000032 sec.
Output:-
Explanation:- In this query, we are using an INNER JOIN with the sales_details table and the derived table.
STEP1: The derived table created in the sub-query “SELECT sale_person_name, MAX(no_products_sold) AS MaxSale FROM sale_details GROUP BY sale_person_name” gets the sales_person_name and maximum no_products_sold grouped by sales_person_name. The alias name given to this derived table is sd2.
STEP2: Select all the details from the sales_details table in the outer-query alias name is sd1.
STEP3: Finally, doing an INNER JOIN on the derived table and sales_details table ON sale_person_name AND ON no_products_sold from sales_details table, MAX(no_products_sold) from the derived table.
We hope this article provided a good understanding of the alias while using the derived tables. Good Luck !!!
