Hi Benjamin, Wow, that sure sorted that problem out... I had to rejig it slightly to get it to work, but this is the final working version:
SELECT b.id, p.part_code, p.product_type, p.description, po.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 po ON po.po_id = b.op_id WHERE bh.basket_id = 4 GROUP BY b.id, p.part_code, p.product_type, p.description, po.options, b.price, b.quantity, total, bh.basket_id, p.options Many thanks, now I'll work out why I couldn't do that so I can better understand it. Kevin ----- Original Message ----- From: "Benjamin Pflugmann" <[EMAIL PROTECTED]> To: "Kevin Smith" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, February 01, 2003 4:32 AM Subject: Re: SQL Syntax > 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
