Problems with a query mysql -

i have 3 tables

table name 1: chair fields table 1: id (int) - model (varchar) // primary index: id

table name 2: color fields table 2: id (int) - color (varchar) // primary index: id

table name 3: chair2color fields table 3: id_chair(int) - id_color(int) // primary index: id_chair-id_color

every chair can have different colors: red or green or (red , green).

some values table 1 (chair):

1 - modela 2 - modelb 3 - modelc 

some values table 2 (color):

1 - red 2 - green 

some values table 3 (chair2color):

1 - 1 2 - 2 3 - 1 3 - 2 

i want chairs models order color in way:

select chair.id id_chair chair  left join chair2color on chair.id=chair2color.id_chair  order field (chair2color.id_color,'1','2') 

the result is:

id_chair 1 2 3 

my problem chairs red appear in first place (it's ok). then, have chairs green , chairs green , red.

i green , red chairs appear before green chairs since red too.

my desired result (and think correct one) be:

id_chair 1 3 2 

how it? :(

you want have distinct chair ids anyway, can build string of colors group_concat() , order this.

select id_chair, group_concat(id_color order id_color) colors chair  left join chair2color on chair.id=chair2color.id_chair  group id_chair order colors 

• see working live in sqlfiddle

if want have specific order, can achieve field() function. read more here.

select id_chair, group_concat(id_color order id_color) colors chair  left join chair2color on chair.id=chair2color.id_chair  group id_chair order field(group_concat(id_color order id_color), 4,3,5,1,2) 

the group_concat(id_color order id_color) in order by implicitly cast integer. therefore works (see live here).

if want reverse order, meaning sort descending, have reverse order in field() function, too.

select id_chair, group_concat(id_color order id_color) colors chair  left join chair2color on chair.id=chair2color.id_chair  group id_chair order field(group_concat(id_color order id_color), 2,1,5,3,4) desc