Example of many-to-many relationship

Question

I haven't found any examples of MYSQL many-to-many relationships either here or on Google. I would like to see a very simple example using php mysql to display the results of the database. Can someone write a very simple example?

Answer 1

This is a quick and simple SQL example. I don't think it's necessary to confuse this concept with php. Just retrieve this collection like any other collection.

In this example, there are many names and many colors. People can have multiple favorite colors, and many people can have the same favorite color. Therefore it is a many-to-many relationship.

***** 表 **********

person
--------
id - int 
name - varchar

favColor
-------------
id - int 
color - varchar

person_color
------------
person_id - int (与person中的id匹配)
color_id - int (与favColor中的id匹配)



****** 示例查询 ******

SELECT name, color 
FROM person 
    LEFT JOIN person_color ON (person.id=person_id)
    LEFT JOIN favColor ON (favColor.id=color_id)


****** 示例查询的结果 *******

Name - Color
---------------
John - Blue
John - Red
Mary - Yellow
Timmy - Yellow
Suzie - Green
Suzie - Blue
等等...

Is this helpful?

Answer 2

Example scenario: Students and courses at a university. A given student may be enrolled in multiple courses, and a course will typically have many students.

Sample table, simple design:

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

Find all students registered for a course:

SELECT
    `Student`.*
FROM
    `Student`
    JOIN `CourseMembership` ON `Student`.`StudentID` = `CourseMembership`.`Student`
WHERE
    `CourseMembership`.`Course` = 1234

Find all courses taken by a specific student:

SELECT
    `Course`.*
FROM
    `Course`
    JOIN `CourseMembership` ON `Course`.`CourseID` = `CourseMembership`.`Course`
WHERE
    `CourseMembership`.`Student` = 5678