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.


Comments

Post a Comment

Popular posts from this blog

python - mat is not a numerical tuple : openCV error -

-
i have write down code showing error ma not getting it: please help: showing mat not numerical tuple: import cv import cv2 capture = cv2.videocapture("j.3gp") while(1): _, frame1 = capture.read() grayimage1 = cv2.cvtcolor(frame1, cv2.color_bgr2gray) _, frame2 = capture.read() grayimage2 = cv2.cvtcolor(frame2, cv2.color_bgr2gray) differenceimage = cv2.absdiff(grayimage1,grayimage2) thresholdimage = cv2.threshold(differenceimage,25,255,cv2.thresh_binary) cv2.imshow("difference image", differenceimage) cv2.imshow("threshold image", thresholdimage) cv2.imshow("image", frame1) k = cv2.waitkey(30) & 0xff error arising : ----------------------------------------------------------------------------------------- traceback (most recent call last): file "desk.py", line 15, in <module> cv2.imshow("threshold image", thresholdimage) typeerror: mat not numerical tuple ...
Read more

c# - MSAA finds controls UI Automation doesn't -

-
i'm working on automating windows application. i'm using teststack white framework. i've hit problem. program has 'window' object cannot see inside of. white shows no controls inside of it. inspect.exe shows no controls inside of either when running in ui automation mode. if switch inspect msaa see controls inside fine. there anyway me use msaa c# handle on these controls? if can identify msaa functions need, can use p/invoke call them c#. here's example article doing msaa: http://www.codeproject.com/articles/38906/ui-automation-using-microsoft-active-accessibility also, pinvoke.net can used identify iaccessible (msaa) functions: http://www.pinvoke.net/search.aspx?search=iaccessible&namespace=[all] here's answer along lines: msaa com-based? finally, alternative p/invoke, might able use tlbimp.exe create wrapper assembly oleacc.dll, , access msaa functions through it. i'm not sure if works msaa, it's worth try. as e...
Read more

wordpress - .htaccess: RewriteRule: bad flag delimiters -

-
pulling hair out on one. have following .htaccess file custom redirects in addition wordpress's default settings. when tested on local wamp server worked fine after moving production i'm not getting rewriterule: bad flag delimiters error in server log , after while site goes down internal server error any appreciated. in advance! <ifmodule mod_rewrite.c> rewriteengine on rewritebase / rewriterule ^rockford_weddings___welcome\.html$ /wedding/ [r=301,l] rewriterule ^blog/?$ /journal/ [r=301,l] rewriterule ^blog/(.*)$ /$1 [r=301,l] rewriterule ^portraitinvestment/?$ /portraitinvestment\.pdf [r=301,l] rewriterule ^weddinginvestment/?$ /weddinginvestment\.pdf [r=301,l] rewriterule ^holidaycards2014/?$ /holidaycards2014\.pdf [r=301,l] </ifmodule> # begin wordpress <ifmodule mod_rewrite.c> rewriteengine on rewritebase / rewriterule ^index\.php$ - [l] rewritecond %{request_filename} !-f rewritecond %{request_filename} !-d rewriterule . /index.php [l] </...
Read more