Examples for working with Relational Division

example_data.sql

/* Tables and data for Relational Division Example */
CREATE TABLE `skill` (
  `idSkill` int(11) NOT NULL AUTO_INCREMENT,
  `Skill` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idSkill`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1


CREATE TABLE `person` (
  `idPerson` int(11) NOT NULL AUTO_INCREMENT,
  `person_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idPerson`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

CREATE TABLE `person_skill` (
  `idPerson` int(11) NOT NULL,
  `idSkill` int(11) NOT NULL,
  PRIMARY KEY (`idPerson`,`idSkill`),
  KEY `fk_person_skill_Skill1` (`idSkill`),
  CONSTRAINT `fk_person_skill_person` FOREIGN KEY (`idPerson`) REFERENCES `person` (`idPerson`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_person_skill_Skill1` FOREIGN KEY (`idSkill`) REFERENCES `Skill` (`idSkill`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1


INSERT INTO `person` (`idPerson`, `person_name`) VALUES
(1, 'Tom'),
(2, 'Dick'),
(3, 'Harriet'),
(4, 'Alice');

INSERT INTO `skill` (`idSkill`, `Skill`) VALUES
(1, 'Linux'),
(2, 'PHP'),
(3, 'MySQL'),
(4, 'Drupal');

INSERT INTO `person_skill` (`idPerson`, `idSkill`) VALUES
(1, 1),
(4, 1),
(1, 2),
(2, 2),
(4, 2),
(1, 3),
(2, 3),
(3, 3),
(4, 3),
(1, 4),
(3, 4),
(4, 4);

inlist_example.sql

/**
 * An in list performs an OR not an AND
 */
SELECT p.person_name, s.skill FROM `test`.`person` p
  inner join person_skill ps on ps.idPerson = p.idPerson
  inner join skill s on s.idskill = ps.idskill
where skill in ('Drupal', 'PHP' , 'MySQL')
order by p.idperson

relational_division.sql

/* List people with all four skills */
SELECT  s.person_name
FROM    Person s INNER JOIN
                 (SELECT     ps.idPerson
                  FROM          person_skill ps
                  WHERE      ps.idskill IN (1,2,3,4)
                  GROUP BY ps.idperson
                  HAVING      COUNT(*) =
                       (SELECT     COUNT(*)
                        FROM          skill
                        WHERE      (idskill IN (1,2,3,4)))
                  ) filter ON s.idPerson = filter.idPerson
ORDER BY s.person_name

relational_division2.sql

/* Another example using interrogation of in list not sub query */
SELECT     s.person_name
FROM         Person s INNER JOIN
                      (SELECT     ps.idPerson
                        FROM          person_skill ps
                        WHERE      ps.idskill IN (1, 2, 3)
                        GROUP BY ps.idperson
                        HAVING      COUNT(*) = (Length('1, 2, 3') - Length(REPLACE('1, 2, 3', ',', '')) + 1)
                        ) filter ON s.idPerson = filter.idPerson
ORDER BY s.person_name

relational_division3.sql

/** Example adapted from Joe Celko's explanation of relational division:
 *  https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/
 */
SELECT DISTINCT person_name
  FROM person_Skill AS PS1 inner join person p on p.idperson = ps1.idperson
  WHERE NOT EXISTS
       (SELECT *
          FROM skill
         WHERE idskill in (2,3,4) and NOT EXISTS
               (SELECT *
                  FROM person_Skill AS PS2
                 WHERE (PS1.idperson = PS2.idperson)
                   AND (PS2.idskill = skill.idskill)));