CODEWITHSUNDEEP
phpmysql
yarek
yarek

Reputation: 12034

How to retrieve values from junction table as a String?

I have 3 tables in MYSQL, VideoCategory is the junction table between Video and category

Video
Category
VideoCategory

it means: each video can bleong to many categories (VideoCategory) table.

My goal is to retrieve a "SMART" way of these values: it means: Video, Comma Delimied String of Categories

ex: 

"video1", "1, 2, 3"
"video2", "1, 4"
"video"3", ""

(video 3 has no categories assigned)

Any idea on how to do that without using mysql loops ?

Upvotes: 1

Views: 38

Answers (1)

Mureinik
Mureinik

Reputation: 311073

group_concat and a couple of joins should do the trick:

SELECT video.name, 
       GROUP_CONCAT(category.name ORDER BY category.name SEPARATOR ', ')
FROM   video v
JOIN   videocategory vc ON v.id = videocategory.video_id
JOIN   category c ON videocategory.category_id = c.id

Upvotes: 1

Related Questions