Selecting rows connected with set of rows from second table

Question

I have three tables:

project (idproject, name)

color (idcolor, name)

project_has_color (idproject, idcolor)

Now I need to select projects connected with set of colors, for example: blue (1), red (2), green (3):

SELECT p.idproject, p.name
FROM project p, project_has_color c
WHERE p.idproject=c.idproject AND c.idcolor IN (1,2,3)

gives me projects connected with one or more given idcolor, and I need projects connected with all of them - but I can't figure out how to achieve this?

Answer 1

This may work

SELECT p.idproject, p.name
FROM project p JOIN project_has_color c USING (idproject)
WHERE c.idcolor IN (1,2,3)
HAVING count(*)=3;

provided that project can't be associated with color twice (i.e. (idproject,idcolor) is unique). or

HAVING
    count(CASE WHEN c.idcolor=1 THEN 1 ELSE NULL)>0
    AND count(CASE WHEN c.idcolor=2 THEN 1 ELSE NULL)>0
    AND count(CASE WHEN c.idcolor=3 THEN 1 ELSE NULL)>0;

otherwise.

Answer 2

Using JOINs is the most reliable way:

SELECT p.idproject, 
       p.name
  FROM PROJECT p
  JOIN PROJECT_HAS_COLOR phc ON phc.idproject = p.idproject
  JOIN COLOR c1 ON c1.idcolor = phc.idcolor
               AND c1.idcolor = 1
  JOIN COLOR c2 ON c2.idcolor = phc.idcolor
               AND c2.idcolor = 2
  JOIN COLOR c3 ON c3.idcolor = phc.idcolor
               AND c3.idcolor = 3

Using:

  SELECT p.idproject, 
         p.name
    FROM PROJECT p
    JOIN PROJECT_HAS_COLOR phc ON phc.idproject = p.idproject
    JOIN COLOR c ON c.idcolor = phc.idcolor
   WHERE c.idcolor IN (1, 2, 3)
GROUP BY p.idproject, p.name
  HAVING COUNT(*) = 3

...relies on your data model stopping an idcolor value from being associated to a project more than once. You can't use HAVING COUNT(DISTINCT *) in MySQL, so 2+ relationships of the same project to duplicate idcolor values would return false positives.