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
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?
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'
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'
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
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.