Unanswered: Converting an MS Access Query to MySQL Query!
Hello can anyone help me convert the following MS Access Query to a MySQL pass through query.
INSERT INTO va_categories ( category_name, parent_category_id, category_path, is_showing )
SELECT First([va_c2k].SubCategory) AS [SubCategory Field], [va_c2k].cat1, "0," & [cat1] & "," AS category_path, "1" AS is_showing
GROUP BY [va_c2k].cat1, "0," & [cat1] & ",", [va_c2k].SubCategory
HAVING (((Count([va_c2k].Category))>1) And ((Count([va_c2k].SubCategory))>1));
I can do most of it myself but I making a mistake around the "0," & [cat1] & "," AS category_path. What I go so far is this
INSERT INTO va_categories (category_name, parent_category_id, category_path, is_showing)
SELECT SubCategory AS 'SubCategory Field', cat1, '0,''va_c2k.cat1'',' AS category_path, '1' AS is_showing
GROUP BY va_c2k.cat1, '0,''va_c2k.cat1'',', va_c2k.SubCategory
HAVING COUNT(va_c2k.Category)>1 AND COUNT(va_c2k.SubCategory)>1;
The problem is I not getting the value for field va_c2k.cat1 in the va_categories.category_path. It should read something like 0,6, but I just get 0,'cat1',.
Thanks guys for the help. Your code r937 nearly worked but not quite. It’s only inserting 9 entries instead of 70. This has to do with the fact that there are only 9 cat1 entries. But now that I've got the , CONCAT('0,' , cat1 , ',') bit I think I can solve the rest.
But don't fright as I'll be back with a query that uses a module, I know that is going to be an issue.