Re: error in your SQL syntax

[Daniel Sousa]

Thanks, works fines.

I use access because i donīt know a GUI tool that make SQL querys more easy.

Thanks all again,

Daniel Sousa
  ----- Original Message ----- 
  From: [EMAIL PROTECTED] 
  To: Daniel Sousa 
  Cc: mysql@lists.mysql.com 
  Sent: Wednesday, 26 January, 2005 14:57
  Subject: Re: error in your SQL syntax



  Here is your original query, reformatted merely so that we humans can read it 
better: 

  SELECT DISTINCT specials.specials_id 
          , products_to_categories.products_id 
          , categories.parent_id 
          , products_description.products_name 
          , products.products_price 
          , products.products_tax_class_id 
          , products.products_image 
          , specials.specials_new_products_price 
          , languages.languages_id 
  FROM languages 
  INNER JOIN 
  ( 
          ( 
                  (specials INNER JOIN 
                          (products_to_categories 
                          INNER JOIN categories 
                                  ON products_to_categories.categories_id = 
categories.categories_id 
                          )ON specials.products_id = 
products_to_categories.products_id 
                  ) 
                  INNER JOIN products 
                          ON specials.products_id = products.products_id 
          ) 
          INNER JOIN products_description 
          ON specials.products_id = products_description.products_id 
  ) ON languages.languages_id = products_description.language_id 
  WHERE 
  ( 
          ( 
                  (categories.parent_id)=285 
          ) AND ( 
                  (languages.languages_id)=1 
          ) 
  ) 

  This query design stinks (reeks) of being autogenerated by M$ Access. The 
excessive use of parentheses when they aren't needed and the nested JOINs just 
complicate the query unnecessarily. 

  May I suggest a simplification? 


  SELECT DISTINCT specials.specials_id 
          , products_to_categories.products_id 
          , categories.parent_id 
          , products_description.products_name 
          , products.products_price 
          , products.products_tax_class_id 
          , products.products_image 
          , specials.specials_new_products_price 
          , languages.languages_id 
  FROM categories 
  INNER JOIN products_to_categories 
          ON products_to_categories.categories_id = categories.categories_id    
     
  INNER JOIN products 
          ON products.products_id = products_to_categories.products_id 
  INNER JOIN specials 
          ON specials.products_id = products.products_id 
  INNER JOIN products_description 
          ON products.products_id = products_description.products_id 
  INNER JOIN languages 
          ON products_description.language_id = languages.languages_id 
  WHERE categories.parent_id=285 
          AND languages.languages_id=1; 

  I have also noticed in my Windows command shell that it does not process 
extremely long lines in "pastes" from the clipboard well. If you copied that 
straight from Access to a MySQL prompt, it would have been just one long line 
of information and the DOS command processor would have eventually stopped 
taking input mid-query. I suspect that is what caused your otherwise 
"acceptable" (and I use that term loosely ;-)  ) query to be invalid. The last 
third of it never made it into the MySQL CLI. 

  When I break my queries into shorter lines (human friendly) and paste them 
into the MySQL command line interface (CLI), everything works just fine. Just 
copy the entire query (line breaks and all) onto the clipboard and paste it at 
the MySQL prompt (if that's how you are doing it) and see if it works now. 
Notepad is my best friend when working in the CLI. I compose and format long 
queries in Notepad then copy-paste into MySQL. I know it's doing it the "hard 
way" (yes, I have and do use the GUI tools too) but it's how I prefer to 
analyze certain issues. 

  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine 


  "Daniel Sousa" <[EMAIL PROTECTED]> wrote on 01/26/2005 06:45:32 AM:

  > I have a problem.
  > 
  > 1064 - You have an error in your SQL syntax near '(((specials INNER 
  > JOIN (products_to_categories INNER JOIN categories ON products' at line 2
  > 
  > SELECT DISTINCT specials.specials_id, products_to_categories.
  > products_id, categories.parent_id, products_description.
  > products_name, products.products_price, products.
  > products_tax_class_id, products.products_image, specials.
  > specials_new_products_price, languages.languages_id FROM languages 
  > INNER JOIN (((specials INNER JOIN (products_to_categories INNER JOIN
  > categories ON products_to_categories.categories_id = categories.
  > categories_id) ON specials.products_id = products_to_categories.
  > products_id) INNER JOIN products ON specials.products_id = products.
  > products_id) INNER JOIN products_description ON specials.products_id
  > = products_description.products_id) ON languages.languages_id = 
  > products_description.language_id WHERE (((categories.parent_id)=285)
  > AND ((languages.languages_id)=1))
  > 
  > 
  > 
  > i run this query in my computer and work, but in the internet server donīt.
  > 
  > If anyone can solve this problem answer me.
  > 
  > Daniel Sousa