title: SQL query to get all products, categories and meta data woocommerce/wordpress
body: <p>I'm stuck trying to create a query that returns me the parameters:</p>
  
  <p>ID, post_title, post_content, category, ID_category, SKU, name, stock, price</p>
  
  <p>At this moment I have this:</p>
  
  <pre><code>SELECT 
   p.ID,
   p.post_title,
   `post_content`,
   `post_excerpt`,
   t.name AS product_category,
   t.slug AS product_slug,
   tt.term_taxonomy_id AS tt_term_taxonomia,
   tr.term_taxonomy_id AS tr_term_taxonomia,
   MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
   MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as regular_price,
   MAX(CASE WHEN pm1.meta_key = '_sale_price' then pm1.meta_value ELSE NULL END) as sale_price,
   MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku 
  
   FROM wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)
   LEFT JOIN   wp_term_relationships AS tr ON ( tr.object_id = p.ID )
   LEFT JOIN   wp_term_taxonomy AS tt ON ( tt.taxonomy = 'product_cat' AND tr.object_id = tt.term_taxonomy_id)
   LEFT JOIN   wp_terms AS t ON ( t.term_id = tt.term_id )
   WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish' AND p.post_content &lt;&gt; ''
   GROUP BY p.ID,p.post_title
  </code></pre>
  
  <p>And it is giving me correctly the products and the metadata but it is  very difficult for me to get the category ID and the category name in this query and I haven't been able to find information on the net.</p>
  
  <p>Thanks.</p>