I tried something like:
SELECT wp_posts.*
FROM wp_posts wp_postmeta
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
WHERE wp_postmeta.meta_key = ‘Number’ AND wp_postmeta.meta_value = ’12’
But it doesn´t seem to work…
The query below should list all your Custom Fields with their associated posts. You should be aware that there may be CF’s added by WordPress, such as ‘_edit_lock’ and ‘_edit_last’, and others added by various plugins.
SELECT m.*, p.* FROM wp_postmeta m, wp_posts p
WHERE m.post_id = p.ID
AND p.ID IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = 'Number'
AND meta_value = 12)
ORDER BY m.post_id, m.meta_id
Thanks for the query!
I tried it but I get lots of (duplicate) entries for each ID.
One question… If I wanted to do this in reverse, I mean list all posts with their associated Custom Fields, what would the query be…?
Ok… I have modified the query a bit and it kind of works:
SELECT m. * , p. *
FROM wp_postmeta m, wp_posts p
WHERE m.post_id = p.ID
AND p.post_status = 'publish'
AND m.meta_key = 'Number'
ORDER BY m.post_id, m.meta_id
LIMIT 0 , 30
But what if I wanted to add 2 meta_keys … ?
If you still want to restrict to posts having a CF of ‘Number’ with a value of 12, try this, replacing ‘key2’, ‘key3’ with the other keys you want:
SELECT m.*, p.* FROM wp_postmeta m, wp_posts p
WHERE m.post_id = p.ID
AND p.ID IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = 'Number'
AND meta_value = 12)
AND p.post_status = 'publish'
AND m.meta_key IN ('Number','key2','key3')
ORDER BY m.post_id, m.meta_id
Add as many other keys to the IN clause as you wish.
