asked on

WordPress post_meta Query and Join tables

Query WordPress database.

I want to query the post_meta table and find the ID's of a product that has a SKU but an empty warehouse location. I can get either or to work but not both. I believe it is because one meta_value is empty and the other has an entry?
How do I join the tables and simplify this query?

SELECT p.ID, mp.post_id, p.post_title, mp.meta_value 
FROM wp_posts AS p, wp_postmeta AS mp 
WHERE (p.post_status='publish' AND p.post_type = 'product')
AND (p.ID = mp.post_id) 
AND (mp.meta_key = '_simple_product_location_field' AND mp.meta_value = '') 
AND (mp.meta_key = '_sku' AND mp.meta_value !='');

Open in new window

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Granlund

ASKER

@chris, I need both of these to be true:
AND ((mp.meta_key = '_simple_product_location_field' AND mp.meta_value = '') OR (mp.meta_key = '_sku' AND mp.meta_value !=''));
I need _simple_product_location_field to be empty and I need _sku to NOT be empty. Those are the records I am looking for.

Thanks in advance.

Chris Stanyon

Hi,

They can't both be true at the time which is why you need an OR statement in there. In simple terms, you can't possibly have the meta_key = _simple_product_location_field AND have the meta_key = _sku !

Robert Granlund

ASKER

@chris, I re-read what I wrote and I was off. What I meant is, I want to get the value of _sku where _simple_product_location_field is empty.