Correlated Subquery Not Working

Question

I'm trying to query the Wordpress Posts and PostMeta tables. Due to Wordpress's "Entity Attribute Value" design of its PostMeta table, it's difficult to query multiple meta values per single query. A correlated sub query is needed.

Here's my current SQL. I'm using the subquery to join the PostMeta table to itself via the Post_ID column, but the inner query is not recognizing the outer query's Post_ID column.

select p.id, p.post_date, tmp.product_name, pm.meta_value as provider_id
from wp_postmeta pm join wp_posts p on p.id=pm.post_id 
join ( 
    select post_id, meta_value as product_name from wp_postmeta 
    where meta_key = 'product_name' and post_id = pm.post_id 
) tmp on tmp.post_id=p.id 
where pm.meta_key='provider_id' and p.post_type='product_listing';

Answer 1

Sorry, I found the correlated subquery needs to go in the SELECT clause and not be used as a JOINed table.

select p.id, p.post_date, pm.meta_value as provider_id,
( 
    select meta_value from wp_postmeta 
    where meta_key = 'product_name' and post_id = pm.post_id 
) as product_name 
where pm.meta_key='provider_id' and p.post_type='product_listing';