Hi.
On Fri 2003-01-31 at 15:46:37 -0000, [EMAIL PROTECTED] wrote:
> Hi All,
>
> Can anyone help me get this query working in MySQL, this was created using
> Access, but it doesn't port well for MySQL syntax:
>
> SELECT basket.id,
> products.part_code,
> products.product_type,
> products.description,
> product_options_1.options,
> basket.price, basket.quantity,
> basket.price*basket.quantity AS total
> FROM (products LEFT JOIN product_options ON
> products.prod_id=product_options.prod_id)
> RIGHT JOIN (product_options AS product_options_1 RIGHT JOIN (basket_header
> INNER JOIN basket ON basket_header.basket_id=basket.basket_id) ON
> product_options_1.po_id=basket.op_id) ON products.prod_id=basket.prod_id
> GROUP BY basket.id, products.part_code, products.product_type,
> products.description, product_options_1.options, basket.price,
> basket.quantity, basket.price*basket.quantity, basket_header.basket_id,
> products.options
> HAVING (((basket_header.basket_id)=4));
Reformatting for readability that is:
SELECT basket.id,
products.part_code,
products.product_type,
products.description,
product_options_1.options,
basket.price,
basket.quantity,
basket.price*basket.quantity AS total
FROM ( products
LEFT JOIN product_options
ON product_options.prod_id = products.prod_id )
RIGHT JOIN
( product_options AS product_options_1
RIGHT JOIN
( basket_header
INNER JOIN basket
ON basket.basket_id = basket_header.basket_id )
ON product_options_1.po_id = basket.op_id )
ON products.prod_id = basket.prod_id
GROUP BY basket.id, products.part_code, products.product_type,
products.description, product_options_1.options,
basket.price, basket.quantity, total,
basket_header.basket_id, products.options
HAVING basket_header.basket_id=4;
> Here is the error message MySQL reports:
>
> ERROR 1064: You have an error in your SQL syntax near '(product_options AS
> product_options_1 RIGHT JOIN (basket_header INNER JOIN baske' at line 9
>
> Seems MySQL doesn't like the RIGHT JOIN syntax. Any ideas to the correct
> syntax?
Oh, RIGHT JOINs are fine. What it doesn't like are the parenthesis, I
think. So simply reordering the joins (and by that replacing RIGHT
JOINs with LEFT JOINs were appropriate and vice versa) should do the
trick.
FROM ( ( basket_header
INNER JOIN basket
ON basket.basket_id = basket_header.basket_id )
LEFT JOIN product_options AS product_options_1
ON product_options_1.po_id = basket.op_id )
LEFT JOIN
( products
LEFT JOIN product_options
ON product_options.prod_id = products.prod_id )
ON products.prod_id = basket.prod_id
Now, a lot of the parenthesis are redundant.
Written this way, it becomes more obvious, that product_options (not
product_options_1) is neither referenced by a other table in an ON
clause nor used in the select part, so what is the reason to include
it to begin with? It's redundant.
Additionally, I don't see the reason for the HAVING clause. IMHO the
condition would be as good in the WHERE clause (where the optimizer
can make better use of it).
Aside from that, I prefer table aliases to get rid of the long names,
so the end result would look like
SELECT basket.id,
p.part_code, p.product_type, p.description,
po1.options,
b.price, b.quantity,
b.price*b.quantity AS total
FROM basket_header bh
INNER JOIN basket b ON b.basket_id = bh.basket_id
LEFT JOIN products p ON p.prod_id = b.prod_id
LEFT JOIN product_options po1 ON po1.po_id = b.op_id
HAVING bh.basket_id = 4;
GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options,
b.price, b.quantity, total, bh.basket_id, p.options
(I did not rename po1 to po in order to avoid confusion.)
HTH,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
