how do I concatenate rows in a mysql query

Question

table 1 -------- pid | name 1 someone 2 another table2 -------- bid | valu 1 drum 2 guitar 3 flower 4 cake table3 ------ id | pid | bid | pref 1 1 3 yes 2 1 1 maybe 3 1 2 no 4 2 4 definately 5 2 2 6 2 3 no

So as you can see I have 3 simple tables where the third one is used to create a mapping between table 1 and 2 along with some additional data. Now I need to write a query to display the valu and pref in a concatenated string based on the pid,

So against pid = 1, the expected output is something like flower yes, drum maybe, guitar no....so How do I write this query?

I tried( pretty much a blind guess):

SELECT opa.name, GROUP_CONCAT(CONCAT(opb.valu,' ',opc.pref) SEPARATOR ',') AS myChoice From table_1 opa INNER JOIN table_3 opc ON opc.pid = opa.pid INNER JOIN table_2 opb ON opb.bid = opc.bid

Any help is appreciated.

this should help stackoverflow.com/questions/4336985/…

nandu
– nandu

2013-04-03 12:08:37 +00:00
Commented Apr 3, 2013 at 12:08 — nandu
– nandu, Commented Apr 3, 2013 at 12:08

echo_Me · Accepted Answer · 2013-04-03 12:11:35Z

your query is right you just forgot the GROUP BY

 SELECT opa.name, GROUP_CONCAT(CONCAT(opb.valu,' ',opc.pref) SEPARATOR ',') AS myChoice From table1 opa INNER JOIN table3 opc ON opc.pid = opa.pid INNER JOIN table2 opb ON opb.bid = opc.bid group by opc.pid

DEMO HERE

Collectives™ on Stack Overflow

how do I concatenate rows in a mysql query

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related