Mysql join table result as one row

Question

I have two 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 this output:

output

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

i write this query:

SELECT prj.ptitle
     , GROUP_CONCAT(pflow1.pactiontxt) men
     , GROUP_CONCAT(pflow2.pactiontxt) women 
  FROM prj 
  JOIN prjflow pflow1 
    ON prj.id = pflow1.pid 
   AND pflow1.paction = 1
  JOIN prjflow pflow2 
    ON prj.id = pflow2.pid 
   AND pflow2.paction = 2;

but output is:

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

My Query when the number of rows of men and women have been equal, working properly but i want that works at any case.

thanks a lot and excuse me for poor english writing

Answer 1

Just use conditional aggregation:

SELECT prj.ptitle,
       GROUP_CONCAT(CASE WHEN prjflow.paction = 1 THEN prjflow.pactiontext END ORDER BY prjflow.id) as men,
       GROUP_CONCAT(CASE WHEN prjflow.paction = 2 THEN prjflow.pactiontext END ORDER BY prjflow.id) as women
FROM prj JOIN
     prjflow 
     ON prj.id = prjflow.pid
GROUP BY prj.ptitle;

This will also fix two potential problems with your query. The first is performance. If some of the titles have large numbers of men and women, then the query has to process a cartesian product. The second is semantic. If some titles have men but not women or women without men, then the two joins will filter them out.

Here is a SQL Fiddle demonstrating it.

Do note that the suggested output seems inconsistent with the input data. This produces the output:

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

I see no reasonable way to exclude man2 from the list, so I assume that is a typo.