I wanted to export the CSV file from the database and import it to Woocommerce. I can get all the fields correctly with Sql join, but I can’t get the category. Since there were parent and child categories
My query is here :
I have also attached the sql file .
SELECT
p.id AS ID,
p.title AS Name,
p.short_description AS ‘Short description’,
p.description AS Description,
p.barcode AS Barcode,
p.pre_tax_retail_price,
p.active AS ‘In stock’,
GROUP_CONCAT(DISTINCT cat.title) AS Categories,
CONCAT_WS(‘,’,
GROUP_CONCAT(DISTINCT CONCAT(‘https://cerberusnutrition.seemaakter.com/normbel/wp-content/uploads/2023/11/’, CASE WHEN s_pdf.file_name LIKE ‘%a.png’ THEN s_pdf.file_name ELSE NULL END) ORDER BY s_pdf.file_name),
GROUP_CONCAT(DISTINCT CONCAT(‘https://cerberusnutrition.seemaakter.com/normbel/wp-content/uploads/2023/11/’, CASE WHEN s_pdf.file_name NOT LIKE ‘%a.png’ THEN s_pdf.file_name ELSE NULL END) ORDER BY s_pdf.file_name)
) AS Images,
pdf.file_name AS Technical_Data_Sheet_PDF
FROM
jiri_jkshop_products AS p
LEFT JOIN
jiri_jkshop_products_categories AS c ON p.id = c.product_id
LEFT JOIN
system_files AS s_pdf ON p.id = s_pdf.attachment_id AND s_pdf.content_type = ‘image/png’ AND s_pdf.field = ‘images’
LEFT JOIN
jiri_jkshop_categories AS cat ON cat.id = c.category_id
LEFT JOIN
system_files AS pdf ON p.id = pdf.attachment_id AND pdf.content_type = ‘application/pdf’ AND pdf.field = ‘attachments’
GROUP BY
p.id;