Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    26

    Unanswered: Problems with LEFT JOIN

    I've just upgraded from MySQL 4.0.13 to 5.0.41 and all my code works fine except for the SQL statement below. I've determined that the problem is the LEFT JOIN part, i.e. if I remove it the rest of the statement works ok, however I do need to have p.rating in so it's not really an option to leave it out.

    I've searched the web and tried all different JOIN combinations but to no avail. Is it possible that someone here could kindly tell me why the statement below doesn't work now under 5.0.41 and what would be the best way to fix it.

    Many thanks.

    BTW, my computer is XP Pro with Apache 2.0.59 and ActivePerl 5.8.8.82


    SELECT DISTINCT(CONCAT_WS(' ', e.manufacturer, e.model)) AS product, c.category, p.rating
    FROM electronics AS e
    INNER JOIN categories AS c ON c.sid=e.sid
    LEFT JOIN product_rating AS p ON p.product=CONCAT_WS(' ', e.manufacturer, e.model)
    WHERE c.category='Televisions'
    GROUP BY product
    ORDER BY product ASC

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    works? doesn't work?

    how does mysql let you know that the query doesn't work? is there a message?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2005
    Posts
    26
    Thank you for your reply.

    That's the weird thing, there is no error message at all. MySQL thinks it is working fine but the expected results are not right. I know it isn't working as it should do because if I leave the LEFT JOIN part out - the expected result would for example be 10 records (which would be correct), but with LEFT JOIN in it returns only 3 records (why 3 I don't know) and with the data in the 3 records being all jumbled up.

    The table "product_rating" is a seperate table within the database and if the product is the same as in the electronics table then it will output a product rating.

    As I said before it works perfectly under 4.0.13 but not for 5.0.41. For some reason the LEFT JOIN part in the statement is not right. Is there another way I could rewrite the SQL statement without using LEFT JOIN, i.e. an internal SELECT statement?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps it's getting confused between the column called product and the expression to which you've assigned a column alias called product

    try it like this --
    Code:
    SELECT DISTINCT 
           p.product
         , c.category
         , p.rating
      FROM categories AS c 
    INNER 
      JOIN electronics AS e 
        ON e.sid = c.sid 
    LEFT 
      JOIN product_rating AS p 
        ON p.product = CONCAT_WS(' ', e.manufacturer, e.model) 
     WHERE c.category = 'Televisions'
    GROUP 
        BY p.product
    ORDER 
        BY p.product ASC
    please note: DISTINCT is not a function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2005
    Posts
    26
    Problem solved.

    I tried writing the statement as you mentioned and it gave the same results. So I went back to my original SQL statement and renamed the "AS product" to "AS product_whatever" and it works fine.

    You were correct in that MySQL 5.0 doesn't allow column alias's to be called the same as table column names, where as MySQL 4.0 is more relaxed with the rule.

    Thank you very much for your help, it's much appreciated.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, my bad, i got turned around

    of course the SELECT, GROUP BY, and ORDER BY clauses should use the CONCAT expression instead of p.product, since p.product is from the right table
    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
  •