Selecting data as a single row when using select join in mysql

Question

How can I get data from a mysql query in a single row when selecting data from two tables using a join?

"id"    "itemId"    "itemTempId"    "itemName"                  "userId" "postId"
"1"     "US1"       "T001"          "Samsung Galaxy Note 5"     "1"      "1"

"id"    "itemId"    "itemTempId"    "itemImageName"     "userId" "postId"
"1"     "US1"       "T001"          "front.jpg"         "1"      "1"
"2"     "US1"       "T001"          "side-left.jpg"     "1"      "1"
"3"     "US1"       "T001"          "side-right.jpg"    "1"      "1"
"4"     "US1"       "T001"          "back.jpg"          "1"      "1"

Right now, when I do like below to select 1 row from the first table and related rows from the second table, I get all data in every row which also repeats when I print it using php.

select a.itemName, b.itemImageName from amga a left join amgb b on a.userId = b.userId where a.userId = 1;

Current Output

"itemName"                  "itemImageName"
"Samsung Galaxy Note 5"     "front.jpg"
"Samsung Galaxy Note 5"     "side-left.jpg"
"Samsung Galaxy Note 5"     "side-right.jpg"
"Samsung Galaxy Note 5"     "back.jpg"

Desirable Output or some other variation that wont repeat the itemName and that can easily be output using php.

"itemName"                  "itemImageName"
"Samsung Galaxy Note 5"     "front.jpg","side-left.jpg","side-right.jpg","back.jpg"

In the final results the title is output only once, whereas the image names need to be looped through. Like this:

"Samsung Galaxy Note 5"
"front.jpg","side-left.jpg","side-right.jpg","back.jpg"

possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — arleslie, Aug 21 '14 at 05:08

score 2 · Answer 1 · answered Aug 21 '14 at 05:07

2

select a.itemName, GROUP_CONCAT(b.itemImageName) 
from amga a 
left join amgb b 
on a.userId = b.userId 
where a.userId = 1
group by a.itemName

Use DISTINCT into GROUP_CONCAT if you dont want duplicates.

answered Aug 21 '14 at 05:07

Mihai

26,325
7
66
81

In your statement, is it possible to use a `limit 1` on the `itemImageName` column so I get only 1 image when I need to output only one image, like for search results etc? – Norman Aug 21 '14 at 05:27
@Norman You could use SUBSTRING_INDEX on GROUP_CONCAT results to get one result Here is a random fiddle example http://www.sqlfiddle.com/#!2/fb704/6 selecting the secound largest value from each group. – Mihai Aug 21 '14 at 05:30

score 2 · Accepted Answer · answered Aug 21 '14 at 05:08

You need grouping with the GROUP_CONCAT function. Please try this query:

select a.itemName, group_concat(b.itemImageName) 
from amga a left join amgb b on a.userId = b.userId 
where a.userId = 1 
group by a.itemName;

score 2 · Answer 3 · answered Aug 21 '14 at 05:08

2

The group_concat operator is what you're looking for:

SELECT    item_name, GROUP_CONCAT(item_image_name)
FROM      amga
LEFT JOIN amgb ON amga.userId = b.userId
WHERE     amge.userId = 1
GROUP BY  item_name

answered Aug 21 '14 at 05:08

Mureinik

297,002
52
306
350

In your statement, is it possible to use a `limit 1` on the `itemImageName` column so I get only 1 image when I need to output only one image, like for search results etc? – Norman Aug 21 '14 at 05:22
@Norman you'd have to replace `amgb` with a subquery, and use `limit` there. Otherwise, the `limit` will be applied to the complete query, which isn't what you want. – Mureinik Aug 21 '14 at 05:28

Selecting data as a single row when using select join in mysql

3 Answers3