Too many AND conditions

Hi trying to run a query on a Wordpress database and my current query is returning an empty set when it should be returning at least 1 result, could this be because of too many "AND’ conditions?

SELECT * FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key='main_video' AND wp_postmeta.meta_value=1 AND wp_postmeta.meta_key= 'main_video' AND wp_postmeta.meta_value= 'Alexander Armstrong'

geez, i go to the beer store and you guys figure it out nicely while i’m gone :slight_smile:

here’s another way –

SELECT wp_posts.* 
  FROM wp_posts
INNER
  JOIN ( SELECT post_id
           FROM wp_postmeta 
          WHERE meta_key = 'main_video' AND meta_value = 1
             OR meta_key = 'artiste' AND meta_value = 'Alexander Armstrong'
         GROUP
             BY post_id
         HAVING COUNT(*) = 2 ) AS b
    ON b.post_id = wp_posts.ID 

if you would like to discuss the benefits of this method over the multi join, let me know

There is no such thing as “too many AND conditions”.
If there is a row in your database that adheres to all criteria you should get it back, if there are no such row(s) you won’t get it back. It’s as simple as that.
Without the actual data I’m afraid we can’t help you any further though.

everybody would be better off, jamessy, if you would adopt some minimal formatting conventions for the sake of human beings, not system software

i mean, mysql has no trouble handling line breaks and extra spacing, so what’s holding you back?

:slight_smile:

okay, here’s your query, reformatted –

SELECT * 
  FROM wp_posts
     , wp_postmeta 
 WHERE wp_posts.ID = wp_postmeta.post_id

   AND wp_postmeta.meta_key = 'main_video'
   AND wp_postmeta.meta_value = 1

   AND wp_postmeta.meta_key = 'main_video'
   AND wp_postmeta.meta_value = 'Alexander Armstrong'

rémon, can you see what’s wrong with this now? :wink:

Yeah I agree, just got caught up in “copy and paste” moment. Sorry, just corrected the query and issue still stands.


SELECT * 
 FROM wp_posts
    , wp_postmeta 
WHERE wp_posts.ID = wp_postmeta.post_id

  AND wp_postmeta.meta_key = 'main_video'
  AND wp_postmeta.meta_value = 1

  AND wp_postmeta.meta_key = 'artiste'
  AND wp_postmeta.meta_value = 'Alexander Armstrong'




I do, yeah … :blush:

jamessy, a row can’t have two different values at the same time. The field wp_postmeta.meta_key either has the value ‘main_video’, or it has the value ‘artiste’, but it can’t have both values at once.

It seems that what you’re looking for is something like


SELECT
   something
 , anything
 , just_not_the_dreaded_star
FROM
  wp_posts AS wp_p
INNER JOIN
  wp_postmeta AS wp_pm1
  ON
  wp_p.ID=wp_pm1.post_id
INNER JOIN
  wp_postmeta AS wp_pm2
  ON
  wp_p.ID=wp_pm2.post_id
WHERE wp_pm1.meta_key = 'main_video'
  AND wp_pm1.meta_value = 1
 
  AND wp_pm2.meta_key = 'artiste'
  AND wp_pm2.meta_value = 'Alexander Armstrong'

although I’m sure Rudy knows a better way to write that :slight_smile:

Oh right, so I need to create an alias. Thanks for that.

The worst thing is that I have had Rudy’s book open in front of me for the last 2 hours! feeling a little bit ashamed, especially with the formatting!

It’s not about the alias per se, it’s about using wp_postmeta twice in the same query :slight_smile:

Got you. Works perfectly by the way, thanks again!

Creative solution, Rudy! I’m diggin it!

thanks :slight_smile:

so that was the general form for “must have both of these 2 conditions”

can you see what it would be for “must have at least 2 of these 3 conditions”?

rémon, can you imagine what the join solution would look like for 2 of 3?

Yup, it gets messy real fast. Your way looks a lot better (provided that the meta_key,meta_value pair are unique of course – which I assume they are) and is probably more efficient as well because you just need one join instead of multiple.

Not only that… the aggregate comparison in the subquery reduces the set to only the matching IDs, so your join is hashing an order of magnitude less comparisons, and doing so on ID-to-ID (very fast). It’s a very efficient solution.