Prendre un Snippet

Un schema classique de gestion de tags :

CREATE TABLE Project ( 
  project_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  name VARCHAR(50) NOT NULL, 
  url TEXT NOT NULL, 
  PRIMARY KEY (project_id) 
) ENGINE=MyISAM;
 
CREATE TABLE Tags ( 
  tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  tag_text VARCHAR(50) NOT NULL, 
  PRIMARY KEY (tag_id), 
  INDEX (tag_text) 
) ENGINE=MyISAM; 
 
CREATE TABLE Tag2Project ( 
  tag INT UNSIGNED NOT NULL, 
  project INT UNSIGNED NOT NULL, 
  PRIMARY KEY (tag, project), 
  INDEX rv_primary (project, tag) 
) ENGINE=MyISAM;

Pour récupérer tous les projets taggué php ou mysql :

SELECT p.name 
FROM Project p 
  INNER JOIN Tag2Project t2p 
    ON p.project_id = t2p.project 
  INNER JOIN Tag t 
    ON t2p.tag = t.tag_id 
WHERE t.tag_text IN ('mysql', 'php');

Pour récupérer tous les projets taggué php et mysql :

SELECT p.name 
FROM Project p 
  CROSS JOIN Tag t1 
  CROSS JOIN Tag t2 
  INNER JOIN Tag2Project t2p 
    ON p.project_id = t2p.project 
      AND t2p.tag = t1.tag_id 
  INNER JOIN Tag2Project t2p2 
    ON t2p.project = t2p2.project 
      AND t2p2.tag = t2.tag_id 
WHERE t1.tag_text = "php" 
  AND t2.tag_text = "mysql";

Tiré et adapté de la présentation Join-fu de Jay Pipes