strategy for SQL headers and detail report

Question

I want to get all schools from the mysql school table and get all students of each school in the students table. for a web based report with heading and totals. I could do something like

select * from schools join students on schoolNo = schoolNo order by schoolNo

Howwever this way I think I will have to keep track of current schoolNo and compare with previous schoolNo in order to generate a header and student count totals for each new school.

Alternatively I could do a Select on Schools and additional selects on students for each school found. This is more intuitive but looks very inefficient.

Does mySQL offer a better techniques fot this like specialized reporting tools like Crystal reports.

thanks

Answer 1

You want a list of schools with number of students?

select sc.*, (select count(st.*) from students st where st.schoolNo = sc.schoolNo) as students from schools sc;

NB: You should put an index on schoolNo in students table.