Query select in table with double WHERE condition (WordPress wp

I am trying to extract multiple IDs from wp_postmeta table, with double WHERE condition to be checked in two records:

WHERE (meta_key = 'leather' AND meta_value = 'calf') -> First record
AND
WHERE (meta_key = 'gender' AND meta_value = 'woman') -> Second record

In a shop selling leather goods I need to extract products (based on postmeta) in CALF (leather) for WOMAN (gender).

The condition must be checked on two records with the same ID, as shown in the picture.

I have tried about a dozen different solutions from various posts but with no result until now.

Thank you.

Wiew example in wp_postmeta table

CodePudding user response：

You probably need an inner join for that:

select ID from wp_posts p
INNER JOIN wp_postmeta m1 ON m1.post_id = p.ID AND m1.meta_key = 'leather' AND m1.meta_value = 'calf'
INNER JOIN wp_postmeta m2 ON m2.post_id = p.ID AND m2.meta_key = 'gender' AND m2.meta_value = 'woman'

I had a similar problem, but in my case both `meta_value` needed to be the same (`1`)

I solved it this way:

select ...
    where (
        meta_key in ('field1', 'field2')
        and meta_value = 1
    )
    having count(distinct meta_key) = 2

you probably need a group by statement as well