MySQL Table Join

I have two tables as

tbl_student

student_id  student_name     student_address
1           Mark Anderson    Park Avenue #203 CA  

tbl_student_subjects

RecordID fkStudentID fkSubject
1           1         English
2           1         Zoology
3           1         Botany

How can I开发者_StackOverflow社区 get the student details with all its subject with a single join and student details should not be repeated.

Thanks in advance Umar

select student_id, student_name, student_address, GROUP_CONCAT(fkSubject)
from tbl_student
left join tbl_student_subjects ON tbl_student.student_id = tbl_student_subjects.fkStudentID
group by tbl_student.student_id

GROUP_CONCAT docs. Note that it's length-limited, by default, to 1024 characters, so if you've got any overachiever students whose class details will exceed 1024 characters, the extra class information will get silently dropped.

Something like this

SELECT tbl_student.student_name, tbl_student.student_address, tbl_student_subjects.fksubject 
FROM tbl_student 
INNER JOIN tbl_student_subjects ON tbl_student_subjects.fkstudentID = tbl_student.student_id
WHERE 1=1 
GROUP BY tbl_student.student_id