mysql - Basic multiplicity - Getting multiple data -

i have 2 tables, people , colors

people have id, name , colorid (colors like) colors have id , name

people 1 color easy data

select people.name pname, colors.name cname  people, colors (people.colorid = colors.id , people.id = 1) 

but, how can people likes 2 or more colors?

imho first of all need change schema able store , query kind of data. need move person <-> color relation (pivot/many-to-many) table.

proposed schema

create table people     (`id` int, `name` varchar(32)); create table colors     (`id` int, `name` varchar(32)); create table person_color     (`person_id` int, `color_id` int); 

now can query data. colors person like

select p.name p_name, c.name c_name    person_color pc join people p     on pc.person_id = p.id join colors c     on pc.color_id = c.id   p.id = 1  order c.id 

sample output:

 |  p_name | c_name | -------------------- | person1 |  black | | person1 |    red | | person1 |   blue | 

or likes more 1 color

select p.name p_name, count(*) how_many_colors   person_color pc join people p     on pc.person_id = p.id  group p.id having count(*) > 1 

sample output:

 |  p_name | how_many_colors | ----------------------------- | person1 |               3 | 

or delimited list of colors per person likes more 1 color

select p.name p_name, group_concat(c.name) colors   person_color pc join people p     on pc.person_id = p.id join colors c     on pc.color_id = c.id   group p.id having count(*) > 1 

sample output:

 |  p_name |         colors | ---------------------------- | person1 | black,blue,red | 

or likes black , blue , nothing else

select p.name p_name   person_color pc join people p     on pc.person_id = p.id join colors c     on pc.color_id = c.id   c.name in('black', 'blue')  group p.id having count(distinct c.id) = 2 

sample output:

 |  p_name | ----------- | person1 | 

etc.

here sqlfiddle demo