SQL: Query one table based on join on another table that has multiple matches

If I had two database tables like this:

+-----------------+
| dog     开发者_如何学编程        |
+-----------------+
| id              |
| name            |
| size            |
+-----------------+

+-----------------+
| dog_color       |
+-----------------+
| id              |
| dog_id          |
| color           |
+-----------------+

How would I write a query such that the result returned all dogs that had the colors of black and white? The dog could have other colors too but needs to have both black and white.

Tested solution below using MySQL 5.1.x, returns all dogs that have at least white AND black colors:

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;

should do the trick.

Sample data and test:

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')