MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.3.31-MariaDB


CREATE Table table_toteach (
	`teach-id` char(10),
	`teach` varchar(100),
	`teacher` varchar(100)
);
INSERT INTO table_toteach (`teach-id`,`teach`, `teacher`) VALUES
('1.0','Subj1-','Tname1'),
('1.2','Subj2-','Tname2'),
('1.5','Subj3-','Tname3'),
('2.0','Subj4-','Tname4');
CREATE TABLE table_students (
	student varchar(10)
);
INSERT INTO table_students (student) VALUES
('stname1'),
('stname2'),
('stname3'),
('stname4');
CREATE TABLE table_stpresents (
	student varchar(10),
	`Date` date,
	present varchar(10)
);
INSERT INTO table_stpresents (student,`Date`,present) VALUES
('stname5','2021-08-16','- yes -'),
('stname4','2021-08-16','- yes -'),
('stname3','2021-08-16','- yes -'),
('stname2','2021-08-16','- yes -'),
('stname1','2021-08-16','- yes -'),
('stname1','2021-08-17','- no -'),
('stname2','2021-08-17','- yes -'),
('stname1','2021-08-18','- yes -'),
('stname2','2021-08-18','- yes -'),
('stname5','2021-08-18','- yes -'),
('stname3','2021-08-19','- yes -'),
('stname4','2021-08-19','- yes -'),
('stname5','2021-08-19','- no -');
CREATE TABLE table_timetable (
	`Date` date,
	subj1 varchar(50),
	subj2 varchar(50),
	subj3 varchar(50),
	stnames varchar(150)
);
INSERT INTO table_timetable (`Date`,subj1,subj2,subj3,stnames) VALUES
('2021-08-16','Subj1-','Subj2-','Subj3-','stname1, stname2, stname3'),
('2021-08-16','Subj1-','Subj3-','Subj2-','stname1, stname4, stname2'),
('2021-08-16','Subj1-','Subj2-','Subj3-','stname2, stname3, stname1'),
('2021-08-17','Subj2-','Subj1-','Subj3-','stname1, stname3, stname5'),
('2021-08-17','Subj2-','Subj1-','Subj1-','stname2, stname1, stname4'),
('2021-08-17','Subj1-','Subj4-','Subj2-','stname2, stname3, stname5'),
('2021-08-18','Subj1-','Subj3-','Subj2-','stname1, stname2, stname3'),
('2021-08-18','Subj4-','Subj2-','Subj1-','stname2, stname3, stname5'),
('2021-08-18','Subj3-','Subj1-','Subj4-','stname1, stname4, stname5');
with recursive cte as (
      select `Date`,subj1,subj2,subj3,'            ' as stname, concat(stnames, ',') as stnames, 1 as lev
      from table_timetable
      union all
      select `Date`,subj1,subj2,subj3,substring_index(stnames, ',', 1),
             substr(stnames, instr(stnames, ',') + 2), lev + 1
      from cte
      where stnames like '%,%'
     )

select `Date`,subj1,subj2,subj3,stname
from cte
where lev > 1


Date
subj1
subj2
subj3
stname


2021-08-16
Subj1-
Subj2-
Subj3-
stname1

2021-08-16
Subj1-
Subj3-
Subj2-
stname1

2021-08-16
Subj1-
Subj2-
Subj3-
stname2

2021-08-17
Subj2-
Subj1-
Subj3-
stname1

2021-08-17
Subj2-
Subj1-
Subj1-
stname2

2021-08-17
Subj1-
Subj4-
Subj2-
stname2

2021-08-18
Subj1-
Subj3-
Subj2-
stname1

2021-08-18
Subj4-
Subj2-
Subj1-
stname2

2021-08-18
Subj3-
Subj1-
Subj4-
stname1

2021-08-16
Subj1-
Subj2-
Subj3-
stname2

2021-08-16
Subj1-
Subj3-
Subj2-
stname4

2021-08-16
Subj1-
Subj2-
Subj3-
stname3

2021-08-17
Subj2-
Subj1-
Subj3-
stname3

2021-08-17
Subj2-
Subj1-
Subj1-
stname1

2021-08-17
Subj1-
Subj4-
Subj2-
stname3

2021-08-18
Subj1-
Subj3-
Subj2-
stname2

2021-08-18
Subj4-
Subj2-
Subj1-
stname3

2021-08-18
Subj3-
Subj1-
Subj4-
stname4

2021-08-16
Subj1-
Subj2-
Subj3-
stname3

2021-08-16
Subj1-
Subj3-
Subj2-
stname2

2021-08-16
Subj1-
Subj2-
Subj3-
stname1

2021-08-17
Subj2-
Subj1-
Subj3-
stname5

2021-08-17
Subj2-
Subj1-
Subj1-
stname4

2021-08-17
Subj1-
Subj4-
Subj2-
stname5

2021-08-18
Subj1-
Subj3-
Subj2-
stname3

2021-08-18
Subj4-
Subj2-
Subj1-
stname5

2021-08-18
Subj3-
Subj1-
Subj4-
stname5


with recursive cte as (
      select `Date`,subj1,subj2,subj3,'            ' as stname, concat(stnames, ',') as stnames, 1 as lev
      from table_timetable
      union all
      select `Date`,subj1,subj2,subj3,substring_index(stnames, ',', 1),
             substr(stnames, instr(stnames, ',') + 2), lev + 1
      from cte
      where stnames like '%,%'
     )
, bySubject as (
select `Date`,subj1 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj2 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj3 as subj, stname
from cte
where lev > 1
)
select `Date`,subj,stname
from bySubject


Date
subj
stname


2021-08-16
Subj1-
stname1

2021-08-16
Subj1-
stname1

2021-08-16
Subj1-
stname2

2021-08-17
Subj2-
stname1

2021-08-17
Subj2-
stname2

2021-08-17
Subj1-
stname2

2021-08-18
Subj1-
stname1

2021-08-18
Subj4-
stname2

2021-08-18
Subj3-
stname1

2021-08-16
Subj1-
stname2

2021-08-16
Subj1-
stname4

2021-08-16
Subj1-
stname3

2021-08-17
Subj2-
stname3

2021-08-17
Subj2-
stname1

2021-08-17
Subj1-
stname3

2021-08-18
Subj1-
stname2

2021-08-18
Subj4-
stname3

2021-08-18
Subj3-
stname4

2021-08-16
Subj1-
stname3

2021-08-16
Subj1-
stname2

2021-08-16
Subj1-
stname1

2021-08-17
Subj2-
stname5

2021-08-17
Subj2-
stname4

2021-08-17
Subj1-
stname5

2021-08-18
Subj1-
stname3

2021-08-18
Subj4-
stname5

2021-08-18
Subj3-
stname5

2021-08-16
Subj2-
stname1

2021-08-16
Subj3-
stname1

2021-08-16
Subj2-
stname2

2021-08-17
Subj1-
stname1

2021-08-17
Subj1-
stname2

2021-08-17
Subj4-
stname2

2021-08-18
Subj3-
stname1

2021-08-18
Subj2-
stname2

2021-08-18
Subj1-
stname1

2021-08-16
Subj2-
stname2

2021-08-16
Subj3-
stname4

2021-08-16
Subj2-
stname3

2021-08-17
Subj1-
stname3

2021-08-17
Subj1-
stname1

2021-08-17
Subj4-
stname3

2021-08-18
Subj3-
stname2

2021-08-18
Subj2-
stname3

2021-08-18
Subj1-
stname4

2021-08-16
Subj2-
stname3

2021-08-16
Subj3-
stname2

2021-08-16
Subj2-
stname1

2021-08-17
Subj1-
stname5

2021-08-17
Subj1-
stname4

2021-08-17
Subj4-
stname5

2021-08-18
Subj3-
stname3

2021-08-18
Subj2-
stname5

2021-08-18
Subj1-
stname5

2021-08-16
Subj3-
stname1

2021-08-16
Subj2-
stname1

2021-08-16
Subj3-
stname2

2021-08-17
Subj3-
stname1

2021-08-17
Subj1-
stname2

2021-08-17
Subj2-
stname2

2021-08-18
Subj2-
stname1

2021-08-18
Subj1-
stname2

2021-08-18
Subj4-
stname1

2021-08-16
Subj3-
stname2

2021-08-16
Subj2-
stname4

2021-08-16
Subj3-
stname3

2021-08-17
Subj3-
stname3

2021-08-17
Subj1-
stname1

2021-08-17
Subj2-
stname3

2021-08-18
Subj2-
stname2

2021-08-18
Subj1-
stname3

2021-08-18
Subj4-
stname4

2021-08-16
Subj3-
stname3

2021-08-16
Subj2-
stname2

2021-08-16
Subj3-
stname1

2021-08-17
Subj3-
stname5

2021-08-17
Subj1-
stname4

2021-08-17
Subj2-
stname5

2021-08-18
Subj2-
stname3

2021-08-18
Subj1-
stname5

2021-08-18
Subj4-
stname5

Date	subj1	subj2	subj3	stname
2021-08-16	Subj1-	Subj2-	Subj3-	stname1
2021-08-16	Subj1-	Subj3-	Subj2-	stname1
2021-08-16	Subj1-	Subj2-	Subj3-	stname2
2021-08-17	Subj2-	Subj1-	Subj3-	stname1
2021-08-17	Subj2-	Subj1-	Subj1-	stname2
2021-08-17	Subj1-	Subj4-	Subj2-	stname2
2021-08-18	Subj1-	Subj3-	Subj2-	stname1
2021-08-18	Subj4-	Subj2-	Subj1-	stname2
2021-08-18	Subj3-	Subj1-	Subj4-	stname1
2021-08-16	Subj1-	Subj2-	Subj3-	stname2
2021-08-16	Subj1-	Subj3-	Subj2-	stname4
2021-08-16	Subj1-	Subj2-	Subj3-	stname3
2021-08-17	Subj2-	Subj1-	Subj3-	stname3
2021-08-17	Subj2-	Subj1-	Subj1-	stname1
2021-08-17	Subj1-	Subj4-	Subj2-	stname3
2021-08-18	Subj1-	Subj3-	Subj2-	stname2
2021-08-18	Subj4-	Subj2-	Subj1-	stname3
2021-08-18	Subj3-	Subj1-	Subj4-	stname4
2021-08-16	Subj1-	Subj2-	Subj3-	stname3
2021-08-16	Subj1-	Subj3-	Subj2-	stname2
2021-08-16	Subj1-	Subj2-	Subj3-	stname1
2021-08-17	Subj2-	Subj1-	Subj3-	stname5
2021-08-17	Subj2-	Subj1-	Subj1-	stname4
2021-08-17	Subj1-	Subj4-	Subj2-	stname5
2021-08-18	Subj1-	Subj3-	Subj2-	stname3
2021-08-18	Subj4-	Subj2-	Subj1-	stname5
2021-08-18	Subj3-	Subj1-	Subj4-	stname5