CODEWITHSUNDEEP
phpmysqlmany-to-many
T. Cem Yılmaz
T. Cem Yılmaz

Reputation: 510

Best practice of Many_to_Many

I can fix this up by using foreach or for loop to my SQL code but I don't want this. I know there could a single SQL code for my answer.

https://stackoverflow.com/a/2923921/1932414

In this answer of many_to_many relationship I can achieve students or courses by given student id or course id.

But how to achieve courses by given different student ids ? I don't want to look for a student but many students.

This is my table structure is the same as given answer.

CREATE TABLE `Student` (
    `StudentID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `FirstName` VARCHAR(25),
    `LastName` VARCHAR(25) NOT NULL,
    PRIMARY KEY (`StudentID`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `Course` (
    `CourseID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `Code` VARCHAR(10) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
    `Name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`CourseID`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `CourseMembership` (
    `Student` INT UNSIGNED NOT NULL,
    `Course` SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (`Student`, `Course`),
    CONSTRAINT `Constr_CourseMembership_Student_fk`
        FOREIGN KEY `Student_fk` (`Student`) REFERENCES `Student` (`StudentID`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `Constr_CourseMembership_Course_fk`
        FOREIGN KEY `Course_fk` (`Course`) REFERENCES `Course` (`CourseID`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET ascii COLLATE ascii_general_ci

For example:

1,5,78,32,54,7,44 id'ed students must give me their courses. How do I do this with a JOIN?

Upvotes: 0

Views: 130

Answers (4)

Narayan Bhandari
Narayan Bhandari

Reputation: 426

I dont have the raw data to check if this works. Please try this if it works for you

SELECT A.StudentID, CONCAT(A.FirstName, A.LastName) AS 'Name' , B.CourseID, B.Code, B.Name
FROM Student A, Course B, CourseMembership C
WHERE A.StudentID=C.Student AND B.CourseID=C.Course
AND A.Student IN (1,5,78,32,54,7,44);

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6844

try it-

SELECT st.studentid,st.first_name,cs.course_id,cs.course_name 
FROM student st 
JOIN coursemembership csm ON csm.student=st.studentid 
JOIN course cs ON cs.courseid=csm.course 
WHERE st.studentid IN (1,5,78,32,54,7,44);

Upvotes: 1

sulimmesh
sulimmesh

Reputation: 763

Why not simply just call something like

SELECT student_id
FROM Students_Courses
WHERE course_id = desired_course_id

Unless I'm misunderstanding what you're trying to get, this will give you tables of all the student_ids for the courses you'll be looking into.

Upvotes: 0

René Hoffmann
René Hoffmann

Reputation: 2815

I modified Hammerite's answer slightly to be used with several IDs:

Find all students registered for a course:

SELECT Student.* FROM Student JOIN CourseMembership ON Student.StudentID = CourseMembership.Student WHERE CourseMembership.Course in (1,5,78,32,54,7,44)

Find all courses taken by a given student:

SELECT Course.* FROM Course JOIN CourseMembership ON Course.CourseID = CourseMembership.Course WHERE CourseMembership.Student in (13,52,178,312,4,27,464)

Notice the bold parts as my edit.

Upvotes: 1

Related Questions