jermiya jermiya - 1 month ago 6
MySQL Question

How can i retrive same forgin key value value datas in a single field as a single table data

i have table like this

table1

nid | name
3 john


table2

tid | address | fkey_id
56 city 3
57 street 3


result

name | address


john city
street

how can i get result like this
i try the below code

SELECT name,address
FROM tbl_name
INNER JOIN tbl_address
ON tbl_name.nid = tbl_address.fkey_id';


result
i got json

{
"name": "john",
"address": "city"
},
{
"name": "john",
"address": "street"
},


but i want the json result like this

{
"name": "john",
"address": "street,city"
}


please help me

Answer

One option is to use GROUP_CONCAT:

SELECT t1.name, GROUP_CONCAT(t2.address) AS address
FROM tbl_name t1
INNER JOIN tbl_address t2
    ON t1.nid = t2.fkey_id
GROUP BY t1.name

Demo here:

SQLFiddle

Comments