Q:

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.

A:

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

