MySQL sort of union

Question

I have to 3 tables :

CREATE TABLE IF NOT EXISTS `Articles` (
  `id` int(11) NOT NULL DEFAULT '',
  ...
  ...
  PRIMARY KEY (`id_article`)
) ENGINE=InnoDB ;

Persons_Articles :

CREATE TABLE IF NOT EXISTS `Persons_Articles` (
  `id_article` int(11) NOT NULL,
  `id_person` int(11) UNSIGNED NOT NULL,
  `role` TINYINT(4) UNSIGNED,
  PRIMARY KEY (`id_article`,`id_person`,`role`)
) ENGINE=InnoDB ;

Persons:

CREATE TABLE IF NOT EXISTS `Persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(64) DEFAULT NULL,
  `lastname` varchar(64) NOT NULL,
  `fullname` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

INSERT INTO Articles VALUES (1, 'A'), (2, 'B'), (3, 'B');
INSERT INTO Persons_Articles VALUES (1, 1, 1);
INSERT INTO Persons_Articles VALUES (1, 2, 2);
INSERT INTO Persons VALUES (1, 'Mick', 'Jagger', 'Mick Jagger');
INSERT INTO Persons VALUES (2, 'Keith', 'Richards', 'Keith Richards');

I look for a query which can return the article with (fullname = 'Mick Jagger' AND role = 1) AND (fullname = 'Keith Richards' AND role = 2)

Any ideas ?

Answer 1

This assumes that you cannot have have a duplicate (name and role) for an article:

  SELECT a.*
    FROM articles a
    JOIN persons_articles pa
      ON pa.id_article = a.id
    JOIN persons p
      ON pa.id_person = p.id
     AND ((p.fullname = 'Mick Jagger' AND pa.role = 1) OR (p.fullname = 'Keith Richards' AND pa.role = 2))
GROUP BY a.id
  HAVING count(*) = 2;

UPDATE

As a side note, I would never store the fullname in the database.. it leads to more work with UPDATEs and INSERTs and if you are not careful, you can end up with mismatched names. Although it is slightly more work, you can always CONCAT() the names if need be.

Answer 2

Try doing the following:

SELECT * FROM Persons P
INNER JOIN Persons_Articles PA ON PA.id_person = P.id
INNER JOIN Articles A ON A.id = PA.id_article
WHERE (P.fullname = 'Mick Jagger' AND PA.role = 1) OR (P.fullname = 'Keith Richards' AND PA.role = 2)