SQL: consulta una tabla basada en join en otra tabla que tiene múltiples coincidencias

Si tuviera dos tablas de database como esta:

+-----------------+ | dog | +-----------------+ | id | | name | | size | +-----------------+ +-----------------+ | dog_color | +-----------------+ | id | | dog_id | | color | +-----------------+ 

¿Cómo escribiría una consulta para que el resultado arrojara todos los perros que tenían los colors de blanco y negro? El perro podría tener otros colors también, pero debe tener tanto en blanco como negro.

La solución probada a continuación utilizando MySQL 5.1.x, devuelve todos los perros que tienen al less colors blanco y negro:

 SELECT dog.*, group_concat(dc_all.color) AS colors FROM dog JOIN dog_color as dc_white on (dog.id = dc_white.dog_id AND dc_white.color = 'white') JOIN dog_color as dc_black on (dog.id = dc_black.dog_id AND dc_black.color = 'black') LEFT JOIN dog_color as dc_all on (dog.id = dc_all.dog_id); 
 select dog.*, count(dog_color.color) as num_colors from dog inner join dog_color on dog_color.dog_id = dog.id where color in ('black', 'white') group by dog.id HAVING COUNT(DISTINCT dog_color.color) = 2; 

debería hacer el truco.

Muestra de datos y testing:

 mysql> select * from dog; +------+-------+------+ | id | name | size | +------+-------+------+ | 1 | fido | 0 | | 2 | ruff | 0 | | 3 | rover | 0 | +------+-------+------+ 3 rows in set (0.00 sec) mysql> select * from dog_color; +------+--------+-------+ | id | dog_id | color | +------+--------+-------+ | 1 | 1 | black | | 2 | 1 | white | | 3 | 1 | brown | | 4 | 2 | black | | 5 | 3 | white | +------+--------+-------+ 5 rows in set (0.00 sec) mysql> select dog.*, count(dog_color.color) as num_colors from dog -> inner join dog_color on dog_color.dog_id = dog.id -> where color in ('black', 'white') -> group by dog.id -> having num_colors = 2; +------+------+------+------------+ | id | name | size | num_colors | +------+------+------+------------+ | 1 | fido | 0 | 2 | +------+------+------+------------+ 1 row in set (0.00 sec) 
 select distinct d.id , d.name , d.size from dog d inner join dog_color c1 on d.id = c1.dog_id and c1.color in ('white') inner join dog_color c2 on d.id = c2.dog_id and c2.color in ('black')