What to do when a subquery returns more than 1 row?

492 Asked by anurhea in SQL Server , Asked on Mar 16, 2023

SELECT * FROM wp_posts WHERE ID IN

(
(SELECT courses  FROM wp_category WHERE CatID =401) OR 
(SELECT  meta_value FROM wp_postmeta WHERE  post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )
)

AND

ID IN (SELECT post_id FROM wp_locations_courses WHERE location_id = 1120)

I am having the problem with above query while i am using IN with sub queries gave me the error Subquery returns more than 1 row why it happens when i am using IN clause

Answered by Ava Black

If subquery returns more than 1 row-

(SELECT courses  FROM wp_category WHERE CatID =401) 

        OR 

        (SELECT  meta_value FROM wp_postmeta WHERE  post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )

This is a condition, but you need n values.

This should work:

(

    ID IN 

        (

            (SELECT courses  FROM wp_category WHERE CatID =401) 

        )

    OR

    ID IN 

        (

            (SELECT  meta_value FROM wp_postmeta WHERE  post_id IN 

                (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )

        )

)

Also take care about subqueries with MySQL. In your case a sub- subquery. Take a look here: https://stackoverflow.com/questions/12356784/mysql-dependent-sub-query-with-not-in-in-the-where-clause-is-very-slow

What to do when a subquery returns more than 1 row?

Your Answer