5
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

6
        (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

0
1
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);

Re-write those nasty subqueries to simply check for existence on the resultant join partitions:

SELECT * FROM wp_posts WHERE EXISTS ( 
    SELECT 1 FROM
       (
            SELECT courses, cat_ID FROM wp_category
                UNION  -- union for OR
            SELECT meta_value AS "courses", cat_ID 
                FROM wp_postmeta, wp_category 
                WHERE post_id=courses AND meta_key ='post_id' 
       ) A, wp_locations_courses -- join for AND
          WHERE 
             ( wp_posts.ID, 1120 ) IN ( post_id, location_id ) AND  -- resultant join #1
             ( wp_posts.ID, 401 ) IN ( courses, cat_ID )  -- resultant join #2
    );

This makes it worthwhile to index and partition the three reference tables accordingly, particularly if there are many potential different cat_IDs with many records on each.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.