Menu
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!!!
498
The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows, it must be handled by set comparison operators (IN, ALL, ANY).
A scalar subquery is a subquery that returns a single value. This is the simplest form of a subquery, and can be used in most places a literal or single column value is valid.
A subquery must be placed on the right side of the comparison operator. Subqueries cannot manipulate their results internally, therefore ORDER BY clause cannot be added into a subquery. You can use an ORDER BY clause in the main SELECT statement (outer query) which will be the last clause.
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.
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
