CODEWITHSUNDEEP
mysqlwordpress
Ferex
Ferex

Reputation: 575

SELECT WHERE value > value2 (value2 retrieved from another row in postmeta table)

I am working on Wordpress, I want to get
ID in 'posts' table where posts.ID = postmeta.post_id AND postmeta.distance > postmeta.radius
And now the SQL statement: 

SELECT DISTINCT p.ID
FROM  posts p
LEFT JOIN  postmeta m ON p.ID = m.post_id
WHERE p.post_type = 'babysitter'
AND p.post_status = 'publish'
AND m.meta_key = 'distance' 
AND ( m.meta_value > (SELECT meta_value 
                       FROM postmeta 
                       WHERE meta_key = 'radius' ))

Besides the problem of avoiding nested SELECT I don't get what I want

Upvotes: 0

Views: 45

Answers (1)

user5992977
user5992977

Reputation:

Well, you explanation seems weird.. You say postmeta.distance > postmeta.radius but you select it from hlp_postmeta , also M alias seem not to do anything. So, I'm guessing you have a few typos here, Either you want it from postmeta like you said, and then I think this is what you need:

SELECT DISTINCT p.ID
FROM  posts p
LEFT JOIN  postmeta m ON p.ID = m.post_id
LEFT JOIN  postmeta m2 ON p.ID = m2.post_id
WHERE p.post_type = 'babysitter'
    AND p.post_status = 'publish'
    AND m2.meta_key = 'distance'
    AND m.meta_key = 'radius'
    AND m2.meta_value > m.meta_value

Or you its not like you said and its from hlp_postmeta and then:

SELECT DISTINCT p.ID
FROM  posts p
LEFT JOIN  postmeta m ON p.ID = m.post_id
LEFT JOIN  hlp_postmeta m2 ON p.ID = m2.post_id
WHERE p.post_type = 'babysitter'
    AND p.post_status = 'publish'
    AND m2.meta_key = 'distance'
    AND m.meta_key = 'radius'
    AND m2.meta_value > m.meta_value

Of course I can be wrong in both cases, if so tell me and I'll correct it.

Upvotes: 1

Related Questions