SQL Query Issues

  • Could anyone let me know why this would not be working? I get confused between OR and AND ??

    I need to get anything that is Blue eyes or Blonde hair. At the moment this gets nothing.

    SELECT wp_posts.ID FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE
	((wp_postmeta.meta_key = 'eyes' AND wp_postmeta.meta_value = 'Blue'
	OR wp_postmeta.meta_key = 'hair' AND wp_postmeta.meta_value = 'Blonde'))
	AND ((wp_postmeta.meta_key = 'availability' AND wp_postmeta.meta_value >= '1'
	AND wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish'))

    Thanks in advance!

Viewing 2 replies - 1 through 2 (of 2 total)
  • popper

    (@julialasarte)

    The problem is each of those values you are checking for is a different entry on the wp_postmeta table, so you’ll never find a row with both ‘eyes’ and ‘availability’ set at the same time. Something like this might work:

    SELECT wp_posts.ID
FROM   wp_posts
       INNER JOIN wp_postmeta
         ON wp_posts.ID = wp_postmeta.post_id
WHERE  ( ( wp_postmeta.meta_key = 'eyes'
           AND wp_postmeta.meta_value = 'Blue' )
          OR EXISTS (SELECT *
                     FROM   wp_postmeta
                     WHERE  wp_postmeta.meta_key = 'hair'
                            AND wp_postmeta.meta_value = 'Blonde'
                            AND wp_postmeta.post_id = wp_posts.ID) )
       AND ( EXISTS(SELECT *
                    FROM   wp_postmeta
                    WHERE  wp_postmeta.meta_key = 'availability'
                           AND wp_postmeta.meta_value >= '1'
                           AND wp_postmeta.post_id = wp_posts.ID) )
       AND wp_posts.post_type = 'post'
       AND wp_posts.post_status = 'publish'
    Thread Starter Lee Wallis

    (@fatwombat)

    WOW! That’s a bit crazy.

    Reading through it, I understand it though, I can read it fine but if I had to do that myself, no hope! Haha

    Thanks heaps for that Julia! Worked a treat ??

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘SQL Query Issues’ is closed to new replies.

Tags