Left outer join is working with SQLite while is not with MySQL

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.

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.

Advertisement

Answer