CODEWITHSUNDEEP
mysql
Lulzim
Lulzim

Reputation: 915

MYSQL query to show all students which have common courses in table

First of all I have this table structure and data as below: enter image description here

I would like to return all the other students for each common course they have for a specific student.

and I am using this query:

SELECT  DISTINCT students.name AS StudentName, students.id as StudentId, courses.id AS   
CourseId,courses.name AS CourseName, universities.id AS University FROM partners
INNER JOIN students ON students.id = partners.student_id
INNER JOIN courses ON courses.id = partners.course_id
INNER JOIN universities ON universities.id = partners.university_id
WHERE students.id != '1' AND partners.course_id IN('6','2','1')
GROUP BY students.name
ORDER BY date

It returns me correct result but the thing is it does not seem to be very effecient especially I am wondering How would I pass on the real app the data in IN() statement.

I hope I am clear.

Thanks

Upvotes: 1

Views: 584

Answers (1)

JohnBo
JohnBo

Reputation: 56

It is more efficient to limit the student selection based on partners.student_id.

Group by on students.name is a very expensive operation. Use the partners.student_id instead.

Without too much studying of your specific query, I guess the use of DISTINCT suggest you have a cartesian product running around. Usually it is smart to eliminate the query to the bare essence and from there enhance it.

Upvotes: 1

Related Questions