Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: LEFT JOIN syntax error on upgrading to MySQL 5.1

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oooohh... lookit that!!!

    great minds think alike on so many levels...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2010
    Posts
    2
    Wow - even the formatting was the same, I'm impressed!

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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •