MySQL Table Join

Question

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 get the student details with all its subject with a single join and student details should not be repeated.

Thanks in advance Umar

Marc B · Accepted Answer · 2011-04-25 15:45:47Z

1

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.

answered Apr 25, 2011 at 15:45

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Umar Adil Over a year ago

Dear Sir,Many Many thanks for your help. This works for me when I need the subjects only with each student, but if I need more attributes also like subject code from subjects table, how can I get the subject code attribute also?

Marc B Over a year ago

just add it to the query. if you need to directly relate the subject and codes, then something like GROUP_CONCAT(CONCAT(fkSubject, ' ', extra, fields, here)) should do the trick.

devasia2112 · Accepted Answer · 2011-04-25 15:46:47Z

0

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

answered Apr 25, 2011 at 15:46

devasia2112

6,0646 gold badges41 silver badges56 bronze badges

Collectives™ on Stack Overflow

MySQL Table Join

tbl_student

tbl_student_subjects

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

tbl_student

tbl_student_subjects

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related