UPDATE to original question for clarity...
Basically I am aiming to get a list of the products and variation products from woocommerce/ Wordpress so I can modify their prices outside of Wordpress.
My server company will not let us use any REST API wrappers which makes things a little difficult using curl (shared server) so I decided to try using SQL.
I was able to pull the products but not the variable products. I wanted to list each item individually with its price in a form where I can manipulate the price and then update remotely.
Problem I have with this initial query is it pulls all products including the main variable product with variations, but I did not want that where variation product exist, I just want the products and variation products. Having the main variable product gives me no control individually over the variations its linked to.
Below is my query to get the details I want for a product but, I cannot get the variations details as I wanted. Can anyone help?
SELECT ID, post_title, guid, post_type, sku_meta.meta_value AS `SKU`,
stock_meta.meta_value AS 'QTY',
salesprice_meta.meta_value AS 'SALEPRICE',
ean_meta.meta_value AS 'EAN',
regularprice_meta.meta_value AS 'RRP'
FROM wp_posts
LEFT JOIN wp_postmeta sku_meta on wp_posts.ID = sku_meta.post_id
AND sku_meta.meta_key = '_sku'
LEFT JOIN wp_postmeta stock_meta on wp_posts.ID = stock_meta.post_id
AND stock_meta.meta_key = '_stock'
LEFT JOIN wp_postmeta salesprice_meta on wp_posts.ID = salesprice_meta.post_id
AND salesprice_meta.meta_key = '_price'
LEFT JOIN wp_postmeta ean_meta on wp_posts.ID = ean_meta.post_id
AND ean_meta.meta_key = '_global_unique_id'
LEFT JOIN wp_postmeta regularprice_meta on wp_posts.ID = regularprice_meta.post_id
AND regularprice_meta.meta_key = '_regular_price'
WHERE post_type = 'product'
Decided to go down the RESP API route as it seems a much more tidy way of accessing data on woo commerce / Wordpress.