I am using a MySQL query to list all product addon variations(swatches), I am able to do this fine, as well as list the product the variation is associated with. The problem I have is listing that products category id as well.
I have no formal training in MySQL so any help and advise on performance etc. will also be of great value!
Here is the code I am using to get the list of addon variations and their products, just need to get the category ID's for those products as well.
$prod_attributes_sql = 'SELECT DISTINCT wt.slug AS term_slug, wtr.term_taxonomy_id, wpp.post_title AS wpost_title, wt.term_id AS wpt_terms, cpt.term_id AS cpt_terms
FROM '
. " {$wc_price_table->cat_price_table_name} cpt"
. ' RIGHT OUTER JOIN wp_terms wt ON cpt.term_id=wt.term_id'
. ' INNER JOIN wp_term_relationships wtr ON wt.term_id = wtr.term_taxonomy_id'
. ' INNER JOIN wp_posts wpp ON wtr.object_id = wpp.ID'
. ' RIGHT OUTER JOIN wp_term_taxonomy ttx on wt.term_id = ttx.term_id'
. ' WHERE ttx.taxonomy IN (\'' . implode("','", $slugsnews) . '\')'
. ' ORDER BY wt.name';
$prod_attributes = $wpdb->get_results($prod_attributes_sql) or die(mysql_error());
Which outputs the list of addon variations and the product they are associated to. But how would I go about including the category id of those products.
Let me know if I need to add any further explanation or code.
Thanks in advance!