MySQL - Select with WHERE on LEFT Join table -

i making select 3 tables, cases, calculactions , positions. positions need devide results 1 more parameter , repairmethod. code below looks fine, , actualy display me results without repair method filter.

current code , results of text looks like:

 select c.vin, c.case_id, c.axrmrs_id, c.date_created,      cl.totalcosts, cl.laborhours, cl.calculationdate, cl.laborcosts,      group_concat(distinct po.text) text      axnmrs_cases c      left join axnmrs_calculations cl on c.case_id = cl.case_id      left join axnmrs_positions po on c.case_id = po.case_id      c.vin='u5yff24128l064909'      group c.vin, c.case_id, c.axrmrs_id 

result (just partt text)

        -------------------------------         |   id  |      text           |         -------------------------------         |   2   |  text1,text2,text3  |         -------------------------------         |   3   |  text4,text5,text6  |         ------------------------------- 

how should looks like:

    --------------------------------------------------     |   id   |      texta       |        textb       |     --------------------------------------------------     |   22   |   tex1, text2    |       text3        |     --------------------------------------------------     |   23   |      text5       |       text4        |     --------------------------------------------------     texta = select text axnmrs_positions repairmethod 'l%'    textb = select text axnmrs_positions repairmethod 'e%' 

i try like, how ever can't make work:

max(case when po.repairmethod = e po.text) 'e' 

is there chance can me this?

edit: fiddle linke: http://sqlfiddle.com/#!9/a76e9/3

you can this:

select ...    , ...    , group_concat(distinct if(po.repairmethod 'l%',po.text,null) order 1) texta    , group_concat(distinct if(po.repairmethod 'e%',po.text,null) order 1) textb    , ... 

the first argument in if expression evaluated boolean, if it's true, second argument return, otherwise third argument returned. , group_concat ignore null values. included order within group_concat make return more deterministic.

if familiar ansi sql: if function shorthand case expression. example, this:

  if(a,b,c) 

is equivalent to:

  case when b else c end 

so this:

  if(po.repairmethod 'e%',po.text,null) 

is equivalent to:

  case when po.repairmethod 'e%' po.text else null end 

(the case expression let omit else null if want.)