10 year later but still better than never...
Categories and tags are not tied to each other. They are both related to post / product. Therefore, you need to query for the all posts/product and grab their IDs, so you can then query for tags where product/post ID is with in this set of IDs.
See the query below for WordPress Posts:
SET @CategoryByID = 1; -- set correct category ID
-- SET @CategoryByName = 'Politics'; -- set correct category name
SELECT DISTINCT t.* FROM wp_posts AS p
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms t ON t.term_id = tt.term_id
WHERE p.post_type="post" -- <<< for wordpress post type
AND p.post_status = 'publish'
AND tt.taxonomy = "post_tag" -- <<< for wordpress post tags
AND p.ID IN
(SELECT p.ID FROM wp_posts AS p
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms t ON t.term_id = tt.term_id
WHERE p.post_type="post" <<< for wordpress post type
AND p.post_status = 'publish'
AND tt.taxonomy = "category" -- <<< for wordpress post category
AND tt.term_taxonomy_id = @CategoryByID -- <<< search category by id (use one)
-- AND t.name = @CategoryByName -- <<< search category by name (use one)
ORDER BY p.ID)
ORDER BY p.ID
See the example query below for WooCommerce Products:
SET @CategoryByID = 18; -- set correct category ID
-- SET @CategoryByName = 'T-SHIRT'; -- set correct category name
SELECT DISTINCT t.* FROM wp_posts AS p
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms t ON t.term_id = tt.term_id
WHERE p.post_type="product" -- <<< for woocommerce post type
AND p.post_status = 'publish'
AND tt.taxonomy = "product_tag" -- <<< for woocommerce tags
AND p.ID IN
(SELECT p.ID FROM wp_posts AS p
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms t ON t.term_id = tt.term_id
WHERE p.post_type="product"
AND p.post_status = 'publish'
AND tt.taxonomy = "product_cat" -- <<< for woocommerce products
AND tt.term_taxonomy_id = @CategoryByID -- <<< search category by id (use one)
-- AND t.name = @CategoryByName -- <<< search category by name (use one)
ORDER BY p.ID)
ORDER BY p.ID