Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    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.

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •