Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question Too many joins causing unknown column error

    When I try to do the query below I end up with error #1054 - Unknown column 'products.id' in 'on clause'

    Code:
    SELECT *
    FROM  products,
             products_categories
             LEFT JOIN categories
             ON       categories.id = products_categories.id
             AND      categories.avail      = 'Y'
             LEFT JOIN category_memberships
             ON       category_memberships.id         = categories.id
             AND      category_memberships.id IS NULL
             LEFT JOIN
                      (SELECT  id,
                               AVG(vote_value) * COUNT(remote_ip) AS customer_rating
                      FROM     product_votes
                      GROUP BY id
                      )
                      AS product_votes
             ON      product_votes.id = products.id
    WHERE  products_categories.id = products.id
    AND      products_categories.id = 254
    AND      products_categories.main = 'Y'
    AND      products.forsale              = 'Y'
    GROUP BY products.id
    ORDER BY customer_rating DESC
    I can't seem to figure out an optimal way to create these joins so that they will work as expected and not produce an error.

    Can someone help me out with this?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    You can't mix implicit and explicit joins. (Well, you can, under some circumstances, but it's never a good idea.)

    So you need to turn that comma between products and products_categories into an INNER JOIN.
    Code:
    ...
    FROM  products INNER JOIN products_categories ON  products_categories.id = products.id
    ...
    then remove that condition from the WHERE.

    See if that doesn't fix it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Tried that. Still not working.

    I didn't start getting the error until I added this join into the mix:
    Code:
    LEFT JOIN
                      (SELECT  id,
                               AVG(vote_value) * COUNT(remote_ip) AS customer_rating
                      FROM     product_votes
                      GROUP BY id
                      )
                      AS product_votes
             ON      product_votes.id = products.id

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    What happens if you re-order the tables?
    Code:
    SELECT *
    FROM  products
             INNER JOIN products_categories
             ON products_categories.id = products.id
             LEFT JOIN
                      (SELECT  id,
                               AVG(vote_value) * COUNT(remote_ip) AS customer_rating
                      FROM     product_votes
                      GROUP BY id
                      )
                      AS product_votes
             ON      product_votes.id = products.id
             LEFT JOIN categories
             ON       categories.id = products_categories.id
             AND      categories.avail      = 'Y'
             LEFT JOIN category_memberships
             ON       category_memberships.id         = categories.id
             AND      category_memberships.id IS NULL
    WHERE  products_categories.id = 254
    AND      products_categories.main = 'Y'
    AND      products.forsale              = 'Y'
    GROUP BY products.id
    ORDER BY customer_rating DESC
    Curiosity: Why the LEFT JOIN to categories??? Surely you wouldn't have any records in products_categories that don't have a corresponding record in categories? Isn't products_categories just a many-to-many table?

    Are you using INNODB or MyIsam? And what version of MySQL?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    WAIT A MINUTE!

    THIS MAKES NO SENSE!
    Code:
    FROM  products
             INNER JOIN products_categories
             ON products_categories.id = products.id
             LEFT JOIN categories
             ON       categories.id = products_categories.id
    HUH???

    You are using the field products_categories.id to join to *BOTH* the products table *AND* the categories table!

    That can't be right!

    Maybe you need to show us your schema? Just what fields *ARE* in your products_categories table???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Curiosity: Why the LEFT JOIN to categories??? Surely you wouldn't have any records in products_categories that don't have a corresponding record in categories? Isn't products_categories just a many-to-many table?

    Are you using INNODB or MyIsam? And what version of MySQL?
    MyISAM on MySQL 5.0.92.

    It is a one-to-one between categories and products_categories. However, products to products_categories is a one-to-many relationship.

    Honestly, I've never had to deal with this many joins before and I'm running up against a wall with my understanding of the way they work.

  • #7
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    WAIT A MINUTE!

    THIS MAKES NO SENSE!
    Code:
    FROM  products
             INNER JOIN products_categories
             ON products_categories.id = products.id
             LEFT JOIN categories
             ON       categories.id = products_categories.id
    HUH???

    You are using the field products_categories.id to join to *BOTH* the products table *AND* the categories table!

    That can't be right!

    Maybe you need to show us your schema? Just what fields *ARE* in your products_categories table???
    Each product can be in multiple categories so have multiple entries in the products_categories table. Not all categories are available to public view, so products whose products_categories are in a category that is hidden, aren't allowed to be shown.

    The connection is products <-> products_categories <-> categories.

    I see where I made in error when I posted the query here. It should look like this:
    Code:
    SELECT *
    FROM  products
             INNER JOIN products_categories
             ON products_categories.productid = products.id
             LEFT JOIN categories
             ON       categories.id = products_categories.categoryid
             AND      categories.avail      = 'Y'
             LEFT JOIN category_memberships
             ON       category_memberships.categoryid         = categories.id
             AND      category_memberships.categoryid IS NULL
             LEFT JOIN
                      (SELECT  productid,
                               AVG(vote_value) * COUNT(remote_ip) AS customer_rating
                      FROM     product_votes
                      GROUP BY productid
                      )
                      AS product_votes
             ON      product_votes.productid = products.id
    WHERE  products_categories.productid = products.id
    AND      products_categories.categoryid = 254
    AND      products_categories.main = 'Y'
    AND      products.forsale              = 'Y'
    GROUP BY products.id
    ORDER BY customer_rating DESC

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    You field names still make no sense.

    NORMALLY, I'd expect to see something like this:
    Code:
    Table: products
        id int primary key
        ... etc ...
    
    Table: categories
        id int primary key
        ... etc ...
    
    Table products_categories
        product_id int references products(id)
        category_id int references categories(id)
    If you don't have two *DIFFERENT* id's in products_categories--one that relates to products, one that relates to categories--then your table design is hosed.

    It's one reason that I never use "id" alone as a field name.

    I would have done:
    Code:
    Table: products
        productid int primary key
        ... etc ...
    
    Table: categories
        categoryid int primary key
        ... etc ...
    
    Table products_categories
        productid int references products(productid)
        categoryid int references categories(categoryid)
    By using names that *TELL* you what the meaning of a field is, it's easier to see mistakes in the JOINs.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Yes, now the query makes sense
    Code:
             ON       categories.id = products_categories.categoryid
    That was *exactly* what I was referring and objecting to.

    So with that fix you still get the error?

    5.0.92 is pretty old. I wonder if you have just run across a bug??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Now *this* part of the query makes no sense:
    Code:
             LEFT JOIN category_memberships
             ON       category_memberships.categoryid         = categories.id
             AND      category_memberships.categoryid IS NULL
    If category_memberships.categoryid really is NULL, then it can't possibly be equal to categories.id. So you will *never* get any records from category_memberships, so why bother JOINing to it?

    FWIW, this is not a complex query. Not even close.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Now *this* part of the query makes no sense:
    Code:
             LEFT JOIN category_memberships
             ON       category_memberships.categoryid         = categories.id
             AND      category_memberships.categoryid IS NULL
    If category_memberships.categoryid really is NULL, then it can't possibly be equal to categories.id. So you will *never* get any records from category_memberships, so why bother JOINing to it?

    FWIW, this is not a complex query. Not even close.
    Re-arranging the query and removing the memberships JOIN fixed the problem.

    Thanks!


  •  

    Posting Permissions

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