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

if had 2 database tables this:

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

how write query such result returned dogs had colors of black , white? dog have other colors needs have both black , white.

tested solution below using mysql 5.1.x, returns dogs have @ least white , black colors:

select dog.*,        group_concat(dc_all.color) colors dog join dog_color dc_white on (dog.id = dc_white.dog_id , dc_white.color = 'white') join dog_color dc_black on (dog.id = dc_black.dog_id , dc_black.color = 'black') left join dog_color dc_all on (dog.id = dc_all.dog_id);