MySQL Join Comma Separated Field
- by neeraj
I have two tables. First Table is a batch table that contain comma separated student id in field "batch"
batch
--------------
id batch
--------------
1 1,2
2 3,4
Second Table is marks
marks
----------------------
id studentid subject marks
1 1 English 50
2 2 English 40
3 3 English 70
4 1 Math 65
5 4 English 66
6 5 English 75
7 2 Math 55
How we can find those students of first batch id =1 who have scored more than 45 marks in English without using sub query.
Problem i found to get this done using a single query is that we can not use IN as an association operator in JOIN statement
What changes are required in below query to make it work?
SELECT * FROM batch
INNER JOIN marks ON marks.studentid IN(batch.batch) where batch.id = 1
