MySQL where-in subquery only returning one row

Question

I have two tables Courses & Students

Say, Students has fields: id, first_name with data

id : first_name

1 : Andrew
2 : Brian
3 : Charles
4 : David
5 : Eric
6 : Fred
7 : George

Say, Courses has two fields: id & students

With data:

id . . . . . . . . 1 . . . . . . 2

students . . . 1,2,5. . . .3,4,6,7

Say I want to find the names of students in course 1

SELECT students FROM courses c WHERE c.id = 1;

yields 1,2,5 as expected

Also

SELECT @students := students FROM courses c WHERE c.id =1;

yields 1,2,5 as expected

And

SELECT s.first_name FROM students s WHERE s.id IN (1,2,5);

yields Andrew, Brian Eric as expected

SET @students := students FROM courses c WHERE c.id =1; SELECT @students;`

yields 1, 2, 5

I would expect

SET @students := students FROM courses c WHERE c.id =1; SELECT s.first_name FROM students s WHERE s.id IN (@students);

to yield Andrew, Brian Eric

but it only returns the first name: Andrew

I'm fully aware that I could/should have a separate course_student look-up table but I'm keen to understand why I'm not getting the result I'm expecting

Where am I going wrong?

Answer 1

Your problem is in how you're using @students; it's defaulting to string* when used in the 'IN' clause.

So your query actually looks like:

SELECT s.first_name FROM students s WHERE s.id IN ("1, 2, 5");

Unfortunately, this ends up as:

SELECT s.first_name FROM students s WHERE s.id IN (1);

Either:

SELECT s.first_name 
FROM students s 
WHERE s.id IN (
               SELECT students FROM courses c WHERE c.id =1
               )

(A classic correlated subquery)

Or:

SELECT s.first_name FROM students s WHERE FIND_IN_SET(`id`, @students)

Or, more preferably, use an INNER JOIN as @JohnTotetWoo describes.

*It appears to be defaulting to a string then the resultant cast to int strips off every number after the first comma. However, it could also be just a quirk of the IN clause that ignores everything else in the variable after the first number.