LEFT JOIN syntax error on upgrading to MySQL 5.1

Code Red · #1 (**permalink**) 04-23-10, 04:33

Hi,

I run a couple of PHP-based websites on a server which until recently was running MySQL 4, but has now upgraded to 5.1. As a result of this upgrade, one of the site management scripts is no longer working and throws up an error at the query stage.

This is the query -

Code:

SELECT p.products_id, p.products_model, p.products_image, p.products_price, svetus.specials_new_products_price as vetus_special, subcvetus.categories_id as vetus_cat_id, svitesse.specials_new_products_price as vitesse_special, subcvitesse.categories_id as vitesse_cat_id FROM products p, categories_vsc subcvetus, products_to_categories_vsc ptocvetus left join specials_vsc svetus ON svetus.products_id = p.products_id, categories_vit subcvitesse, products_to_categories_vit ptocvitesse left join specials_vit svitesse ON svitesse.products_id = p.products_id WHERE p.products_id = ptocvetus.products_id AND ptocvetus.categories_id = subcvetus.categories_id AND p.products_id = ptocvitesse.products_id
AND ptocvitesse.categories_id = subcvitesse.categories_id

The database is used by two separate sites which sell the same products, but which are arranged into unique product category trees and have the capability to have unique special offer prices. This script is used to export the contents of the products database tables to a CSV file to allow the site owner to make modifications using Excel.

The script pulls off each product in turn, then the category it is in on each site, along with a special offer price for each site if it has one - hence the LEFT JOIN - ONs. This worked fine under MySQL 4, but as the syntax for LEFT JOINs is different in 5.1 it doesn't work now.

I know the statements need to be enclosed in parentheses, but no matter what I try I just get different flavours of error. The problem seems to be that you have two sequential LEFT JOINs, but both reference back to the same initial 'products' table reference in the query.

Any suggestions as to how I can fix this? The sites are structured so that potentially I could have several sites running from the same central database, so the query is built in blocks using PHP. I've looked at the script to see if I could get around the problem by querying the database in a different way, but it would involve virtually rewriting the whole script from scratch, and it's huge!

I would appreciate any suggestions or help.

gvee · #2 (**permalink**) 04-23-10, 06:06

Your joins were effectively inner joins, so I've changed your query to use the appropriate join syntax - see if it makes any difference.

Code:

SELECT p.products_id
     , p.products_model
     , p.products_image
     , p.products_price
     , svetus.specials_new_products_price As vetus_special
     , subcvetus.categories_id As vetus_cat_id
     , svitesse.specials_new_products_price As vitesse_special
     , subcvitesse.categories_id As vitesse_cat_id
FROM   products As p
 INNER
  JOIN products_to_categories_vsc As ptocvetus
    ON ptocvetus.products_id = p.products_id
 INNER
  JOIN categories_vsc As subcvetus
    ON subcvetus.categories_id = ptocvetus.categories_id
 LEFT
  JOIN specials_vsc As svetus
    ON svetus.products_id = p.products_id
 INNER
  JOIN products_to_categories_vit As ptocvitesse
    ON ptocvitesse.products_id = p.products_id
 INNER
  JOIN categories_vit As subcvitesse
    ON subcvitesse.categories_id = ptocvitesse.categories_id
 LEFT
  JOIN specials_vit As svitesse
    ON svitesse.products_id = p.products_id

r937 · #3 (**permalink**) 04-23-10, 06:07

Code:

SELECT p.products_id
     , p.products_model
     , p.products_image
     , p.products_price
     , svetus.specials_new_products_price as vetus_special
     , subcvetus.categories_id as vetus_cat_id
     , svitesse.specials_new_products_price as vitesse_special
     , subcvitesse.categories_id as vitesse_cat_id 
  FROM products AS p
INNER
  JOIN products_to_categories_vsc AS ptocvetus 
    ON ptocvetus.products_id = p.products_id 
INNER
  JOIN categories_vsc AS subcvetus
    ON subcvetus.categories_id = ptocvetus.categories_id 
INNER
  JOIN products_to_categories_vit AS ptocvitesse 
    ON ptocvitesse.products_id = p.products_id 
INNER
  JOIN categories_vit AS subcvitesse
    ON subcvitesse.categories_id = ptocvitesse.categories_id
LEFT OUTER
  JOIN specials_vsc AS svetus 
    ON svetus.products_id = p.products_id
LEFT OUTER
  JOIN specials_vit AS svitesse 
    ON svitesse.products_id = p.products_id

r937 · #4 (**permalink**) 04-23-10, 06:08

oooohh... lookit that!!!

great minds think alike on so many levels...

Code Red · #5 (**permalink**) 04-23-10, 06:56

Wow - even the formatting was the same, I'm impressed!

That's done the trick, thanks for the help guys.

gvee · #6 (**permalink**) 04-23-10, 08:28

Well, actually I put a single space before the LEFT/INNER join line and drop the optional OUTER keyword, but yeah, sweet as formatting, eh?

r937 · #7 (**permalink**) 04-23-10, 12:07

and i always move all the OUTER joins to the end of the FROM clause

another thing we did the same was flip over the ON clause columns, so that the column of the table being joined is mentioned first in the ON clause, and the column it links up with (which belongs to a previously-mentioned table) is mentioned second, so it sticks out

Internet Services

Web Development

Digital Marketing

Consumer Tech