MySQL error when trying to get unique values using DISTINCT together with LEFT JOIN

Question

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

Answer 1

SELECT
        DISTINCT sc.c_id,
        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

the DISTINCT keyword must be the first word after select because it will apply to the whole row.
It's as if you say :

SELECT DISTINCT(sc.c_id, c.course_name, ...) FROM ...

you can't say :

 SELECT sc.c_id, DISTINCT(c.course_name, ...) FROM ...

Answer 2

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 GROUPing 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