On Fri, Jan 31, 2003 at 02:07:11PM -0000, Kevin Smith 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 b.id, p.part_code, p.product_type, p.description, po1.options,
> b.price, b.quantity, b.price*b.quantity AS total
> FROM (products AS p LEFT JOIN product_options AS po ON p.prod_id =
> po.prod_id) RIGHT JOIN (product_options AS po1 RIGHT JOIN (basket_header AS
> bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) ON po1.po_id =
> b.op_id) ON p.prod_id = b.prod_id
> GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options,
> b.price, b.quantity, b.price*b.quantity, bh.basket_id, p.options
> HAVING (((bh.basket_id)=4));
Try
FROM (((basket_header AS bh INNER JOIN basket AS b
ON bh.basket_id = b.basket_id)
LEFT JOIN product_options AS po1
ON po1.po_id = b.op_id)
LEFT JOIN products AS p
ON p.prod_id = b.prod_id)
LEFT JOIN product_options AS po
ON p.prod_id = po.prod_id
MySQL tends to be more finicky than Jet about how you group things.
I haven't tried this, but I think it will avoid confusing the MySQL
optimizer.
Bob Hall
---------------------------------------------------------------------
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
