MySQL error when trying to get unique values using DISTINCT together with LEFT JOIN
mysql, sql
Solution
`DISTINCT` is a keyword to be used immediately after `SELECT` (Have a look at the Mysql `SELECT` syntax). You cannot select columns distinctively, you rather do a `SELECT DISTINCT` query. What you possibly want is a `GROUP BY`. But then you need an aggregate function (to tell mysql what to do with the other columns - the ones you are not `GROUP`ing `BY`.) Example using `GROUP_CONCAT` as aggregate function (Which just concatenates all columns in each group using a comma):
SELECT c.course_name, GROUP_CONCAT(sc.c_id),
FROM Courses AS c
LEFT JOIN Student_Courses AS sc ON c.c_id = sc.c_id
WHERE sc.s_id = 4
GROUP BY course_name
Problem
I have two mysql tables, courses and student_courses, I need to get unique course_names from the course table. But I get a erro when trying to execute my query. Heres my query ``` SELECT sc.c_id, DISTINCT c.course_name FROM Courses AS c LEFT JOIN Student_Courses AS sc ON c.c_id = sc.c_id WHERE sc.s_id = 4 ``` This is the error I get ``` CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT c.course_name ``` FROM Courses AS c LEFT JOIN Student_Courses AS ' at line 3