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