database - Mysql : join three tables with cardinality 1:N -

i have database academy school. structure has 3 tables: - first 1 master of subjects. table have on 20 rows.

create table `subjects` (   `id_sb` tinyint(4) not null auto_increment,   `name` varchar(255) default null,   primary key (`id_sb`) )  

-the second 1 contains students data. 1 have on 10000 rows.

create table `students` (   `id_st` int(10) not null auto_increment,   `name` varchar(50) default null,   primary key (`id_st`) )  

-the last 1 has student's qualifications each subject. has around 1000 rows.

create table `qualifications` (   `id_cf` int(10) not null auto_increment,   `id_st` int(10) default null,   `id_sb` int(10) default null,   `value` int(2) default null,   primary key (`id_cf`) ) 

i need export these data in excel format, like:

id:st / name / subject1/ subject2/ subject3/ subject4/..../subject_n (if 1 student has no qualification subject, excel show empty cell column).

how can join these tables? have thought using left join, number of returning rows query increases exponentially.

and problem worse before because have add new table related students 1:n cardinality.

how improve functionality? can't change database schema.

thanks

i think forgot group by, please try following.

select     st.id_st, st.`name`, group_concat(distinct su.`name`) subjects students st left join qualifications qu on     st.id_st = qu.id_st left join subjects su on     su.id_sb = qu.id_sb group st.id_st 

this should display each student once, subjects should separated comma(,) , each subject should appear once per student (but if student has subject).

id - name - subjects
1 - eve - subj1, subj3, subj7

---

but, wasn't asked for, want each subject have it's own column, right?

to that, use subquery in select subjects , use if print yes or no each subject. still need use group_concat. solve in different way.

i use different language separate columns. first need student information , id student subjects, this:

select     st.id_st, st.`name`, group_concat(distinct qu.`id_sb`) subject_ids students st left join qualifications qu on     st.id_st = qu.id_st group st.id_st 

then subjects:

select * subjects 

the last part creating excel. example below written in php:

$excelrows = array(); foreach($students $student){     $excelrow = array($student->id, $student->name);     foreach($subjects $subject){         array_push($excelrow, (in_array($subject->id, $student->subject_ids))?'yes':'no');     }     array_push($excelrows, $excelrow); } 

so, loop students, inside student-loop loop subjects.

---

it isn't tested, think of should work. if fails, please show me have tried , explain why doesn't work.