MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE students (`id` int, `name` varchar(9));
CREATE TABLE subjects (`id` int, `credit` int, `name` varchar(43));
CREATE TABLE student_subjects (`studentId` int, `subjectId` int);
    
INSERT INTO students (`id`, `name`) VALUES
    (1, 'student 1'),
    (2, 'student 2'),
    (3, 'student 3');

INSERT INTO subjects (`id`, `credit`, `name`) VALUES
    (1, 3, 'subject 1'),
    (2, 4, 'subject 2'),
    (3, 4, 'subject 3');

INSERT INTO student_subjects (`studentId`, `subjectId`) VALUES
    (1, 2),
    (2, 2),
    (2, 3),
    (3, 1),
    (3, 3);
SET @subj := 'subject 2'
SELECT st1.name student, su2.name subject
FROM subjects su1
JOIN student_subjects ss1 ON su1.id = ss1.subjectId
JOIN student_subjects ss2 ON ss2.studentId = ss1.studentId
JOIN subjects su2 ON su2.id = ss2.subjectId
JOIN students st1 ON st1.id = ss1.studentId
WHERE su1.name = @subj


student
subject


student 2
subject 2

student 1
subject 2

student 2
subject 3