CODEWITHSUNDEEP
mysql
Chaa22
Chaa22

Reputation: 31

Avoiding duplicates when joining two tables in MySQL

I have written code to extract data for the following tables. The first one being the student table:

Student | Course | Post |
--------|--------|------|
   1    |   67   |  4   |
   2    |   75   |  5   |
   3    |   67   |  8   |

Instructor Table:

    Instructor | Course | Post |
    -----------|--------|------|
         5     |   67   |  9   |
         4     |   75   |  11  |
         7     |   67   |  7   |

I joined the two tables with a left join on the course to get a table of students with their various instructors. However, the result produces duplicates for two instructors in the same course. As you will see below:

Student | Course | Post | Instructor |
--------|--------|------|------------|
   1    |   67   |  4   |     5      |
   1    |   67   |  4   |     7      |
   2    |   75   |  5   |     4      |

Is there a way I can avoid the duplicates and show both instructors for that course? Thanks

Upvotes: 0

Views: 38

Answers (1)

Dylan Su
Dylan Su

Reputation: 6065

Try this:

SELECT s.*, GROUP_CONCAT(i.Instructor) AS Instructor
FROM student_table s LEFT JOIN instructor_table i USING(Course)
GROUP BY s.Student

Upvotes: 1

Related Questions