Clever Geek Handbook

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

I have two mysql tables, courses and student_courses, I need to get unique course names from courses table. But when I try to execute my request, I get an erro.

Here is my request

        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 am getting

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 with LEFT JOIN Student_Courses AS 'on line 3

+2


source to share


2 answers


DISTINCT

is a keyword to be used immediately after SELECT

(see MysqlSELECT

SELECT DISTINCT

GROUP BY

syntax ). You cannot unambiguously select columns, you are rather making a query . Perhaps you need . 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

.) An example using GROUP_CONCAT

as an 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
+5


source 


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 the selection because it will apply to the entire line.
It's like you're saying:

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


you cannot say:

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


source






More articles:


All Articles