DB Design suggestions

I am looking for most optimal DB Solution for the below case.

Consider i have a Parent Table [P1] Which has columns [ ID | StudentID | StudentName ] , and multiple child tables derived from P1, Lets say C1, C2, C3 ... [ This grows ] where every Table's composite primary key is [StudentID , ReportingDate] and other factual data respective to the table .

I have a use case where if give StudentID, i need list of records from all the tables of the given Key in the format [ Table Name | StudentID | ReportingDate ]

Sample Input

Search for STD1

Sample Output

Table Name| Searched Key | ReportingDate

C1 | STD1 |Date1

C3 | STD1 |Date1

C1 | STD1 |Date2

Child tables are different Reports generated based out of StudentID's on different dates and that will be growing.

I can't make joins of a growing Child tables . Lets say i have 30 - 40 Child tables.

Do we have some solution for this ?

Thanks for any help !