I am writing to PHP site for accounting data, payment and attendance of students. Those who come to the tutor.
In MySQL, there is a pays table in which the last student payment is entered. And there is a table of dates , where the dates of visits of each student are added.
Payment is made for 8 classes .
Question: I can’t figure out in any way how can I do to bring out all the students who have to pay and who have the seventh or eighth lesson in a row ? And so that it does not disappear, it has not yet been added to the pays table.

  • one
    If it is not difficult to give the structure of your tables, how do you keep the number of lessons, how are the lessons and students related? Pupils and their payment? - cheops
  • This is similar to important domain logic. If you do not have millions of students, then transfer it from the database to the code — better to a separate method / class and write tests on it. - luchaninov

1 answer 1

SELECT dates.user_id FROM dates LEFT JOIN pays ON pays.user_id = dates.user_id WHERE pays.id IS NULL GROUP BY dates.user_id HAVING COUNT(dates.user_id) IN (7,8)

We take all the records from the dates, join pays with payments, make the condition that no payments have been made, group by user_id and make sure that the user has a condition that he has 7 or 8 classes.

You asked about the number of visits in the second month, while the simplest thing that came up, so it is, it turned out of course somehow collective mine, but okay.

Select all dates, join pays, group by user_id, see if the number of different visit dates is equal to 7 * number of payments or 8 * number of payments, (number of payments at least equal to 1), then the user enters the interval and it can be warned. Pays must have a unique identifier (id), dates must have a visit date (date).

 SELECT dates.user_id, COUNT(DISTINCT pays.id)+1 pcount FROM dates LEFT JOIN pays ON pays.user_id = dates.user_id GROUP BY dates.user_id HAVING COUNT(DISTINCT dates.date) IN (7*pcount, 8*pcount)

How shorter and more effective to write a second, it has not yet come to mind.

  • And what if, in the second month, the number of visits increases by +8? And in the base pays.id will there be payment for the last month? - bakusite
  • one
    @bakusite And you didn’t write about it)) I wrote a solution for SQL for the task, updated the answer) Night, the original solution has not yet come to mind) - Firepro
  • one
    Very cool and brilliant !!! - bakusite
  • one
    @bakusite Thank you for such a high appreciation of the answer :) - Firepro
  • one
    HAVING COUNT(DISTINCT dates.date) IN (7*pcount, 8*pcount) -> HAVING COUNT(DISTINCT dates.date)%pcount IN (7, 8) !!! - E_p