sql many to many code example
Example 1: insert many to many sql
INSERT INTO persons (firstname,lastname) VALUES ('John','Doe');
SET @person_id = LAST_INSERT_ID();
INSERT IGNORE INTO properties (property) VALUES ('property_A');
SET @property_id = LAST_INSERT_ID();
INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);
INSERT IGNORE INTO properties (property) VALUES ('property_B');
SET @property_id = LAST_INSERT_ID();
INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);
INSERT IGNORE INTO properties (property) VALUES ('property_C');
SET @property_id = LAST_INSERT_ID();
INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);
Example 2: how to join tables with many to many relationships
SELECT
p.person_id,
p.name,
a.conference,
b.publication
FROM
person AS p
LEFT JOIN (SELECT
pc.person_id,
c.conference
FROM
person_conference AS pc
INNER JOIN conference AS c
ON pc.conference_id = c.conference_id) AS a
ON p.person_id = a.person_id
LEFT JOIN (SELECT
pp.person_id,
ppp.publication
FROM
person_publication AS pp
INNER JOIN publication AS ppp
ON pp.publication_id = ppp.publication_id) AS b
ON p.person_id = b.person_id