Converting an MS Access Query to MySQL Query!

glav · #1 (**permalink**) 08-29-09, 04:28

Hello can anyone help me convert the following MS Access Query to a MySQL pass through query.

Code:

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
FROM va_c2k
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

Code:

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
FROM va_c2k
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',.

Can anyone explain what i'm doing wrong.

Thanks,
Paul

r937 · #2 (**permalink**) 08-29-09, 07:19

there is no FIRST() function in mysql, so use MIN() instead

Code:

INSERT 
  INTO va_categories 
     ( category_name
     , parent_category_id
     , category_path
     , is_showing )
SELECT MIN(SubCategory) 
     , cat1
     , CONCAT('0,' , cat1 , ',') 
     , 1 
  FROM va_c2k
GROUP 
    BY cat1
HAVING COUNT(Category) > 1
   AND COUNT(SubCategory) > 1

glav · #3 (**permalink**) 08-29-09, 07:53

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.

Thanks again,
Paul

r937 · #4 (**permalink**) 08-29-09, 08:01

Quote:

Originally Posted by glav

It’s only inserting 9 entries instead of 70. This has to do with the fact that there are only 9 cat1 entries.

uh oh, it looks like i forgot to include SubCategory in the GROUP BY

Code:

INSERT 
  INTO va_categories 
     ( category_name
     , parent_category_id
     , category_path
     , is_showing )
SELECT MIN(SubCategory) 
     , cat1
     , CONCAT('0,' , cat1 , ',') 
     , 1 
  FROM va_c2k
GROUP 
    BY cat1
     , Subcategory
HAVING COUNT(Category) > 1
   AND COUNT(SubCategory) > 1

although this makes MIN(Subcategory) irrelevant, yes?

Internet Services

Web Development

Digital Marketing

Consumer Tech