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),
	`tech` varchar(100),
	`teacher` varchar(100)
);
INSERT INTO table_toteach (`teach-id`,`tech`, `teacher`) VALUES
('1.0','Subj1-','Tname1'),
('1.2','Subj2-','Tname1');
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
('stname1','2021-08-16','- yes -'),
('stname2','2021-08-16','- no -'),
('stname1','2021-08-17','- no -'),
('stname2','2021-08-17','- yes -'),
('stname1','2021-08-18','- yes -'),
('stname2','2021-08-18','- yes -');
CREATE TABLE table_timetable (
	`Date` date,
	subj varchar(100),
	stname varchar(10)
);
INSERT INTO table_timetable (`Date`,subj,stname) VALUES
('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','Subj2-','stname2'),
('2021-08-18','Subj2-','stname1');
    SELECT teacher.`teach-id`, tech, teacher, student, MAX(attendance.Date) AS LDUS
    FROM table_timetable tt
    INNER JOIN table_stpresents attendance ON tt.Date = attendance.Date AND tt.stname = attendance.student
    INNER JOIN table_toteach teacher ON tt.subj = teacher.tech
    WHERE attendance.present = '- yes -'
    GROUP BY teacher.`teach-id`, tech, teacher, student
    ORDER BY `teach-id`, student;


teach-id
tech
teacher
student
LDUS


1.0
Subj1-
Tname1
stname1
2021-08-18

1.0
Subj1-
Tname1
stname2
2021-08-17

1.2
Subj2-
Tname1
stname1
2021-08-18

1.2
Subj2-
Tname1
stname2
2021-08-18


    SELECT teacher.`teach-id`, tech, teacher
    , MAX(CASE WHEN student = 'stname1' THEN attendance.Date END) AS stname1
    , MAX(CASE WHEN student = 'stname2' THEN attendance.Date END) AS stname2
    , MAX(CASE WHEN student = 'stname3' THEN attendance.Date END) AS stname3
    , MAX(CASE WHEN student = 'stname4' THEN attendance.Date END) AS stname4
    , MAX(CASE WHEN student = 'stname5' THEN attendance.Date END) AS stname5
    FROM table_timetable tt
    /*INNER JOIN table_students s ON tt.stname = s.student*/
    INNER JOIN table_stpresents attendance ON tt.Date = attendance.Date AND tt.stname = attendance.student
    INNER JOIN table_toteach teacher ON tt.subj = teacher.tech
    WHERE attendance.present = '- yes -'
    GROUP BY teacher.`teach-id`, tech, teacher
    ORDER BY `teach-id`;


teach-id
tech
teacher
stname1
stname2
stname3
stname4
stname5


1.0
Subj1-
Tname1
2021-08-18
2021-08-17
null
null
null

1.2
Subj2-
Tname1
2021-08-18
2021-08-18
null
null
null
teach-id	tech	teacher	student	LDUS
1.0	Subj1-	Tname1	stname1	2021-08-18
1.0	Subj1-	Tname1	stname2	2021-08-17
1.2	Subj2-	Tname1	stname1	2021-08-18
1.2	Subj2-	Tname1	stname2	2021-08-18