Mysql join table result as one row -

i have 2 tables

prj

id | ptitle 1  | prj111 2  | prj222 

prjflow

id | pid | paction | pactiontxt  1 |  1  |    1    |     man1  2 |  1  |    1    |     man2  3 |  1  |    2    |     woman1  4 |  1  |    1    |     man3 

i want output:

output

ptitle | men       | women prj111 | man1,men3 | woman1 

i write query:

select prj.ptitle      , group_concat(pflow1.pactiontxt) men      , group_concat(pflow2.pactiontxt) women    prj    join prjflow pflow1      on prj.id = pflow1.pid     , pflow1.paction = 1   join prjflow pflow2      on prj.id = pflow2.pid     , pflow2.paction = 2; 

but output is:

ptitle | men       | women prj111 | man1,men3 | woman1,woman1 

my query when number of rows of men , women have been equal, working want works @ case.

thanks lot , excuse me poor english writing

just use conditional aggregation:

select prj.ptitle,        group_concat(case when prjflow.paction = 1 prjflow.pactiontext end order prjflow.id) men,        group_concat(case when prjflow.paction = 2 prjflow.pactiontext end order prjflow.id) women prj join      prjflow       on prj.id = prjflow.pid group prj.ptitle; 

this fix 2 potential problems query. first performance. if of titles have large numbers of men , women, query has process cartesian product. second semantic. if titles have men not women or women without men, 2 joins filter them out.

here sql fiddle demonstrating it.

do note suggested output seems inconsistent input data. produces output:

ptitle | men            | women prj111 | man1,man2,men3 | woman1   

i see no reasonable way exclude man2 list, assume typo.