CODEWITHSUNDEEP
mysqlsqljoinsubquery
suz
suz

Reputation: 747

MySQL \ join 2 tables and show results from 2 rows in one column

I have 2 tables. One is a list of proucts, and the second - list of images connected to each product.

PRODUCTS as P

ID | NAME 
1 | apple
2 | plum
3 | carrot
4 | strawberry
5 | pie


IMAGES as IM

PRODUCT_ID | IMAGE | I_NAME
1 | 1 | app_abc.jpg
1 | 2 | apple.jpg
1 | 3 | appleonemoretime.jpg
2 | 1 | plum.jpg
2 | 2 | plum2.jpg
2 | 3 | plum3.jpg
2 | 4 | plum4.jpg
3 | 1 | somecarrot.jpg
4 | 1 | s1.jpg

etc...

Additional info:
- Each product has min 1 image.
- The max amount of images connected with one product is 60. I would like to get: list of products with image names (one row = one product).
- I will search products by product.id - I want to get images in one column, separated by commas, I do not want to get 60 'null' columns.

For instance: if I search of p.id (1, 3) I would like to get something like:

P.ID | IM1.I_NAME  
1 | app_abc.jpg, apple.jpg, appleonemoretime.jpg  
3 | somecarrot.jpg

Is there a way? 'COALESCE' is good for this?

What I have now is: 

select p.id
from products p 
join images im on im.product_id = p.id 
where p.id in (1, 3)

Upvotes: 0

Views: 595

Answers (4)

Ronald Alexander Kailola
Ronald Alexander Kailola

Reputation: 149

try this :

select p.id, group_concat(im.I_NAME separator ',') from products p  left join images im on im.product_id = p.id  where p.id in (1, 3) group by p.id

Upvotes: 1

The Coder
The Coder

Reputation: 496

GROUP_CONCAT is your best friend, your query should look like this:

SELECT p.id, GROUP_CONCAT( im.i_name SEPARATOR '; ' ) AS images
FROM products p 
LEFT JOIN images im ON (im.product_id = p.id)
WHERE p.id IN (1, 3) GROUP BY p.id

Upvotes: 1

Ravinder Gujiri
Ravinder Gujiri

Reputation: 1514

you can use GROUP_CONCAT function

reference: https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Upvotes: 1

Pramod
Pramod

Reputation: 1041

You can use group_concat in query. Try this query,

select p.id, group_concat(im.I_NAME)
from products p 
join images im on im.product_id = p.id 
where p.id in (1, 3)
group by p.id

Upvotes: 1

Related Questions