SQL query for Woocommerce products: Get sku and product tag

Question

With a SQL query I want to get all products with the products SKU and product tag ID, product tag name (but not product category.

I tried the following code, but I don't know how to query product tag ID and product tag name:

SELECT 
 p.ID,
 p.post_title,
 `post_content`,
 `post_excerpt`,
 t.name AS product_category,
 t.term_id AS product_id,
 t.slug AS product_slug,
 t.name,
 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 uzn2_posts p 
LEFT JOIN uzn2_postmeta pm1 ON pm1.post_id = p.ID
LEFT JOIN uzn2_term_relationships AS tr ON tr.object_id = p.ID
JOIN uzn2_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN uzn2_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 <> ''
GROUP BY p.ID,p.post_title

Answer 1

Product variation don't handle any custom taxonomy as categories, tags, attributes.

The taxonomy for product tag is simply product_tag.

In the following SQL query, you will get the product ID, the sku, the tag name and ID (replace "wp_" from the table names with the correct table index if needed):

SELECT p.ID AS product_id,
pm.meta_value AS product_sku,
t.name AS tag_name,
t.term_id AS tag_id
FROM wp_posts p 
LEFT JOIN wp_postmeta pm ON pm.post_id = p.ID
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id 
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE p.post_type = 'product' 
AND p.post_status = 'publish'
AND pm.meta_key = '_sku'
AND tt.taxonomy = 'product_tag'

For product variation type use (where we get the product tag name and ID from the parent variable product):

SELECT p.ID AS product_id,
pm.meta_value AS product_sku,
t.name AS tag_name,
t.term_id AS tag_id
FROM wp_posts p 
LEFT JOIN wp_postmeta pm ON pm.post_id = p.ID
LEFT JOIN wp_term_relationships tr ON p.post_parent = tr.object_id 
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE p.post_type = 'product_variation' 
AND p.post_status = 'publish'
AND pm.meta_key = '_sku'
AND tt.taxonomy = 'product_tag'

Note: Only products that have a product tag will be listed.