how to get all values from many-to-many relation which having some condition

user_14821912 0
Viewed 33

the following is my database schema with data inserted into it:


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);

I want to get all students and all of them subjects if they have a specific subject and I want to include for each student all its subjects. For example, I want to get all students which have subject 2 the result should be like this:

studentName subjectName
student 1 subject 2
student 2 subject 2
student 2 subject 3

I can use where to filter students which have subject 2 like this

select students.name as studentName, subjects.name as subjectName from students
left join student_subjects on students.id = student_subjects.studentId
left join subjects on subjects.id = student_subjects.subjectId
where subjects.id = 2

but it will filter data for other subjects

Any idea to do that? Thanks

mysql
1 Answers
SELECT st1.name student, su2.name subject
-- get needed subject from this table
FROM subjects su1
-- get students list from this table
JOIN student_subjects ss1 ON su1.id = ss1.subjectId
-- get all subjects for these students
JOIN student_subjects ss2 ON ss2.studentId = ss1.studentId
-- get these subjects names from this table
JOIN subjects su2 ON su2.id = ss2.subjectId
-- get these students names from this table
JOIN students st1 ON st1.id = ss1.studentId
-- specify needed subject
WHERE su1.name = @subj

fiddle

is there is a way to drive the query from students table, I mean the form part like this from students – Imad Jomar

Inner join is not sensitive to the source tables ordering (except the fact that the table must be mentioned before any its columns use in any joining condition). You may use

SELECT st1.name student, su2.name subject
FROM students st1 
JOIN subjects su1 
JOIN student_subjects ss1 
JOIN student_subjects ss2 
JOIN subjects su2 ON st1.id = ss1.studentId
                 AND su1.id = ss1.subjectId
                 AND ss2.studentId = ss1.studentId
                 AND su2.id = ss2.subjectId
WHERE su1.name = @subj

In this form the order of tables means nothing.

Or you may specify joining condition to each joining table separately:

SELECT st1.name student, su2.name subject
FROM students st1 
JOIN student_subjects ss1 ON st1.id = ss1.studentId
JOIN subjects su1 ON su1.id = ss1.subjectId
JOIN student_subjects ss2 ON ss2.studentId = ss1.studentId
JOIN subjects su2 ON su2.id = ss2.subjectId
WHERE su1.name = @subj
user_101387340
Related