I have a table with products, orders, prices, date of sell. I need to create a table with the sells for a product in a single day. I used sqlite and this code worked just fine:
/* Groups order date <-> product */ CREATE TEMPORARY TABLE IF NOT EXISTS tableDate AS SELECT DISTINCT A.orderDate, B.productName FROM ordini AS A CROSS JOIN ordini AS B; /* Groups orders by product and date */ CREATE TEMPORARY TABLE IF NOT EXISTS tableOrders AS SELECT COUNT(productQty) AS total, productName, SUM(totalAmount) AS amount, orderDate FROM ordini WHERE (orderStatus >= 3) GROUP BY orderDate, productName; /* Selects the product sells data, grouped day by day */ SELECT orderDate, productName, COALESCE(total, 0) AS total, IFNULL(amount, 0) AS amount FROM (SELECT * FROM tableDate LEFT OUTER JOIN tableOrders ON tableOrders.orderDate = tableDate.orderDate AND tableOrders.productName = tableDate.productName) ORDER BY orderDate;
As I said, this code worked pretty fine with sqlite (with “TEMP VIEW” instead of “TEMPORARY TABLE”), but with mysql I get “Every derived table must have its own alias“. So I have added an alias on the last select:
/* Selects the data of sells, grouped by day */ SELECT orderDate, productName, COALESCE(total, 0) AS total, IFNULL(amount, 0) AS amount FROM (SELECT * FROM tableDate LEFT OUTER JOIN tableOrders ON tableOrders.orderDate = tableDate.orderDate AND tableOrders.productName = tableDate.productName) AS MyAlias ORDER BY orderDate;
and now I get “Duplicate column name ‘productName’“. Now I’m pretty sure I have tried almost every possible combination of table name and productName, but still can’t get rid of that error.
Advertisement
Answer
In the inner query:
SELECT * FROM tableDate LEFT OUTER JOIN tableOrders ON tableOrders.orderDate = tableDate.orderDate AND tableOrders.productName = tableDate.productName
you select all the columns from both tables.
So in the result there are 2 columns named productName
from the 2 tables (the same for the column orderDate
) and this is not allowed.
In the select list specify only one of each column pair and any other column that you want and drop *
:
SELECT d.productName, d.orderDate , ...... FROM tableDate d LEFT OUTER JOIN tableOrders o ON o.orderDate = d.orderDate AND o.productName = d.productName
I used aliases for the table names to shorten the code.