How to display the result of subquery rows as a single column in MySQL?

Question

I have three tables Category, Movies and RelCatMov

Category table

categoryid, categoryName 1 thriller 2 supsense 3 romantic 4 action 5 sci-fi 

Movies Table

 movieid, movieName 1 Avataar 2 Titanic 3 NinjaAssassin 

RelCatMov table

 categoryid, MovieID 1 1 2 2 3 2 4 2 5 2 

Now I want to display the entry as

 MovieName Categories Titanic Suspense,Romantic,Sci-fi,action 

How to do it.

I am writing a request

 select MovieName,(select categoryname from category b,relcatmov c where b.categoryid=c.categoryid and c.movieid=a.movieid) as categories from movies a; Error: Subquery returns more than one row!!! 

How to display the result of rows in a single column?

Please, help!!!

Answer 1

In Oracle, this is called stragg. In MySQL, this is GROUP_CONCAT.

 select MovieName,(select GROUP_CONCAT(categoryname) from category b,relcatmov c where b.categoryid=c.categoryid and c.movieid=a.movieid) as categories from movies a; 

For reference, your problem is that MySQL wants you to return a single value, and instead you return multiple rows.

Answer 2

For a similar need in MS-SQL, I wrote a function that returns me a concatenated list (string). So you can follow this approach.