SQL Multiple WHERE IN

I am trying to select the ID from a wp_posts table where I need to do 3 conditions.

  1. Is the ID matching with the post_id from the wp_postmeta table where the meta_key = ‘gtp_analytics_client_id’ and the meta_value is not empty.

    Read More
  2. Is the ID matching with the post_id from the wp_postmeta table where the meta_key = ‘gtp_conversion_uploaded’ and the meta_value is not equal to 1.

  3. Is the ID matching with the post_id from the wp_postmeta table where the meta_key = ‘gtp_lead_revenue’ and the meta_value is not empty.

I am a beginner with SQL. This is what I have now, but I cannot use multiple IN’s. So I think I need to do it another way.

SELECT ID 
FROM wp_posts 
WHERE ID IN (SELECT post_id 
             FROM wp_postmeta 
             WHERE meta_key = 'gtp_analytics_client_id' AND meta_value != '') 
AND IN (SELECT post_id 
        FROM wp_postmeta 
        WHERE meta_key = 'gtp_conversion_uploaded' AND meta_value != 1)
AND IN (SELECT post_id 
        FROM wp_postmeta 
        WHERE meta_key = 'gtp_revenue' AND meta_value != '')

I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IN (SELECT post_id FROM wp_postmeta WHERE meta_key = ‘gtp_conversion_uploaded’ A’ at line 4

Related posts

Leave a Reply

3 comments

  1. The and is not part of the in operator, it is three separate in operators, so you need the first operand (ID) for all of them:

    SELECT ID 
    FROM wp_posts 
    WHERE ID IN ( ... ) 
    AND ID IN ( ... )
    AND ID IN ( ... )
    

    You could also write that as three joins:

    SELECT
      p.ID
    FROM
      wp_posts p
      INNER JOIN wp_postmeta m1 ON m1.post_id = p.ID AND m1.meta_key = 'gtp_analytics_client_id' AND m1.meta_value != ''
      INNER JOIN wp_postmeta m2 ON m2.post_id = p.ID AND m2.meta_key = 'gtp_conversion_uploaded' AND m2.meta_value != 1
      INNER JOIN wp_postmeta m3 ON m3.post_id = p.ID AND m3.meta_key = 'gtp_revenue' AND m3.meta_value != ''
    
  2. When it can be either of the 3 cases

    SELECT ID 
    FROM wp_posts 
    WHERE ID IN (SELECT post_id 
                 FROM wp_postmeta 
                 WHERE (meta_key = 'gtp_analytics_client_id' AND meta_value != '')
                 OR (meta_key = 'gtp_conversion_uploaded' AND meta_value != 1)
                 OR (meta_key = 'gtp_revenue' AND meta_value != '')
                ) 
    
  3. SELECT p.ID 
    FROM wp_posts p
    JOIN wp_postmeta m on p.id = m.post_id
    group by p.id
    having sum(m.meta_key = 'gtp_analytics_client_id' AND m.meta_value != '') > 0
    and sum(m.meta_key = 'gtp_conversion_uploaded' AND m.meta_value != 1) > 0
    and sum(m.meta_key = 'gtp_revenue' AND m.meta_value != '') > 0