MYSQL vs SQLITE syntax difference .. every derived table must have an alias

user_2094585 0
Viewed 46

I am quite an amateur with SQL (I am a teacher rather than programmer) and have written some code which worked in SQLITE but does not work in MYSQL. I have a table called 'scores' of student names, exercises and scores. As well as returning the completed exercises, I also wish to return those which are not completed. For this I used CROSS JOIN to generate all combinations of student and exercise. In SQLITE this returned null values for students that had not completed a particular exercise, which is useful.

Using this code in MYSQL, it complains that "Every derived table must have its own alias"

I understand this means that a subquery must have an 'AS ...' term. However, no matter where I add it, I still receive that error. I have tried online SQL 'validators' to help me, but still no success.

Can anybody help improve my code please ? Thank you

SELECT othertable.name , othertable.exercise, scores.score
from ((select distinct name from scores)
CROSS JOIN (select distinct exercise from scores)) AS othertable
left join scores
on(scores.name = othertable.name AND scores.exercise = othertable.exercise )
order by othertable.name, othertable.exercise
sqlite mysql
1 Answers

The problem with your code is what the error says, in MySql you must set an alias for each subquery of your query.
But instead of aliasing the 2 cross joined subqueries and the result of their join, drop the parentheses of the subqueries and use 3 aliases:

SELECT n.name, e.exercise, s.score
FROM (SELECT DISTINCT name FROM scores) AS n
CROSS JOIN (SELECT DISTINCT exercise FROM scores) AS e
LEFT JOIN scores AS s
ON s.name = n.name AND s.exercise = e.exercise
ORDER BY n.name, e.exercise
user_104988280
Related