MySQL: LEFT JOIN table ON table.id IN () -

this question has answer here:

• sql query of multiple values in 1 cell 2 answers

i have 2 tables:

orders

+----+------+-------+ | id | name | notes | +----+------+-------+ |  1 | adam |   1,2 | |  2 | ema  |     3 | |  3 | petr |   1,3 | +----+------+-------+ 

notes

+----+---------------------+ | id | text                | +----+---------------------+ |  1 | first note  | |  2 | second note | |  3 | , third note      | +----+---------------------+ 

i need to select row orders , group concat text second table based on orders.notes.

if use statement

select o.name, o.notes     ,group_concat(distinct n.text separator ';') notes_text orders o left join notes n on n.id in (1,2) o.id = 1; 

result expected this first note;this second note

but if use statement need, notes.id in (orders.notes)

select o.name, o.notes     ,group_concat(distinct n.text separator ';') notes_text orders o left join notes n on n.id in (o.notes) o.id = 1 

it returns first text this first note. why?

sqlfiddle

sql dbs not "tear apart" csv value in field. these 3 fragments parse/execute identically:

... n.id in (o.notes) ... n.id in ('1,2') ... n.id = '1,2' 

note quotes. 1,2 treated monolithic string, not 2 separate values spearated comma.

if want use bad table design (you should normalize), use find_in_set() instead.

note chain mysql, , lose portability.