sql - Mysql joining three tables getting to a single table -

sql - Mysql joining three tables getting to a single table -

i have 3 tables. parent, student, student_parent'

parent p_id p_name

student s_id s_name

student_parent s_id p_id

i want form new table reporting functionality, in next format.

s_id | s_name | p_name_1 | p_name_2

since 2 records created 1 pupil such mother , father, want both records single table 1 s_id. have table this,

p_id | s_id | s_name | p_name 01 | 01 | sam | jack 02 | 01 | sam | jill

i want table in next structure.

s_id | s_name | p_name_1 | p_name_2 01 | sam | jack | jill

i have searched not find proper solution.

here curremt sql statement:

select s.student_id, s.first_name, s.last_name, c.first_name fsms_student s, fsms_student_parent_guardian b, fsms_parent_guardian c b.student_id = s.student_id , b.parent_guardian_id = c.parent_guardian_id

i much obliged if provide me solution. give thanks you.

why, have solution:

create table newtable select s.student_id s_id, concat(s.first_name, " ",s.last_name) s_name, count(distinct b.parent_guardian_id) parentcount, group_concat(p_name) fsms_student s, fsms_student_parent_guardian b, fsms_parent_guardian c s.student_id = b.student_id , b.parent_guardian_id = c.parent_guardian_id grouping 1,2

mysql sql