I am trying to produce a list of posts and display a report which shows both some meta\_values for each post, but also a list of all categories to which each post is attached. 

I am using the nifty Exports and Reports module to format the results of my query, and the data I am trying to pull out is a list of Woocommerce products. 

I got as far as:

```
SELECT wp_posts.ID, wp_posts.post_title AS Product, m2.meta_value AS _retail_price,  m4.meta_value AS _stock
FROM wp_posts

LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_price'
LEFT JOIN wp_postmeta AS m4 ON m4.post_id = wp_posts.ID AND m4.meta_key = '_stock'
WHERE wp_posts.post_type IN ('product', 'product_variation')  
AND  wp_posts.post_status = 'publish'
ORDER BY wp_posts.ID ASC

```

That works, I get a list of posts (products) with the relevant meta values populated. 

But I would now like to add a list of categories that each post is listed in, ideally with a column per category. But each post is in multiple categories, and I am stumped as to how to pull out the categories for each one. 

I did: 

```
SELECT wp_posts.ID AS ProdId, wp_posts.post_title AS Product, m1.meta_value AS _retail_price,  m2.meta_value AS _stock, wp_term_relationships.*, wp_terms.* 
FROM wp_posts, wp_term_relationships, wp_terms

LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_price'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_stock'

LEFT JOIN wp_posts posts2 ON wp_term_relationships.object_id = wp_posts.ID
LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
LEFT JOIN wp_terms terms2 ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id

WHERE wp_posts.post_type IN ('product', 'product_variation')  
AND  wp_posts.post_status = 'publish' AND taxonomy = 'product_cat'
ORDER BY wp_posts.ID ASC

```

but that gives me an #1054 - Unknown column 'wp\_posts.ID' in 'on clause' and I think I have my joins in a tangle :-(