Good time to all!

The commonplace task is to create a query (or several) from many tables to create a pivot table.

There are 5 tables:

lessons with fields: id|title|date
evals with the fields id|eval|lesson_id|pupil_id|subject_id|date
subjects with fields: id|title
pupils (students) with fields: id|firstname|lastname
absent (blanks) with fields: id|pupil_id|lesson_id

Create a table in the form of a school magazine page:

Alt text

N is any number (that is, the number of students and lessons is arbitrary).
I solved the problem using cycles in php. It works fine while the records in the tables are few. As soon as there are a couple of million estimates, half a million lessons, then it hangs terribly. Tell me a way to avoid loops. Direct on the right decision. Thank you in advance!

Update

My question is to find out how to get all the data at once. I tried to "blind" all the tables with join:

 select `evals`.`pupil_id` `pupil_id`, `evals`.`lesson_id` `lesson_id`, `evals`.`eval` `eval`, `evals`.`id` `eval_id` from `evals` `evals` join `lessons` `lessons` on `evals`.`lesson_id`=`lessons`.`id` left join `absent` `absent` on `absent`.`pupil_id`=`evals`.`pupil_id` where `evals`.`subject_id`=10 and `lessons`.`date` between '1409515200' and '1420920000'
  • one
    You do requests in a cycle? If yes, then get rid of them and get all the data at once, then no, then give the code of your solution - andreyqin
  • My question is to find out how to get all the data at once. I tried to "blind" all the tables with join: select evals . pupil_id pupil_id , evals . lesson_id lesson_id , evals . eval eval , evals . id eval_id from evals lesson_id = lessons . id left join absent absent on absent . pupil_id = evals . pupil_id where evals . subject_id = 10 and lessons . date between '1409515200' and '1420920000' - Agloval
  • @Agloval, If you are given an exhaustive answer, mark it as correct (click on the check mark next to the selected answer). - Vitalina

1 answer 1

Try something like this:

 select p.lastname, e.date, ifnull(a.lesson_id,e.eval) from pupils p left join evals e on p.id=e.pupil_id left join subjects s on e.subject_id=s.id left join absent a on p.id=a.pupil_id where s.id=?

It would be convenient if the preliminary data were presented in tables. After receiving the answer of the request, is it possible to shove processing on some kind of PL?

  • I process requests for pkhp. This request was pushed into phpmyadmin. Server hanged. In the row tables a bit. The absent is only 111,000, in the rest - no more than 10,000. - Agloval