How to Display result of subquery rows as one column in MySQL?

Question

Hi 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 a the record as

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

How to do this.

I am writing a query

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 one column?

Please help!!!

Answer 1

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

Answer 2

In Oracle it's called stragg. In MySQL it's 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 you're returning several rows instead.