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
    New Coder
    Join Date
    Mar 2004
    Posts
    95
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Help with Multiple Joins

    Hi,

    I am not very good building mysql queries using Joins. I hope someone can help me.

    I have a table 'products' with id, name and category_id fields. I also have a table 'categories' with id and name fields. I also have a table 'product_extra_categories' which allows a product to be included in a category other than its primary one. It has fields id, product_id and category_id.

    I need to display a list of products.id's and the categories.name where the products.category_id = the chosen category number or the product has the chosen category number as an extra category, ie product_extra_categories.product_id = products.id AND product_extra_categories.category_id = categories.id

    I have tried many things and keep stuffing it up. Sorry for my poor explanation. Thanks in advance.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,100
    Thanks
    23
    Thanked 594 Times in 593 Posts
    I don't think
    Code:
    AND product_extra_categories.category_id = categories.id
    is necessary, but don't think it hurts anything either.

    Code:
    WHERE products.id = categories.product_id
    is necessary.

    Something like this (you don't give us the column names so I guess):
    Code:
    select
        p.product_id
        p.product_name,
        c.category_name,
        e.category_name
    from
        products p,
        categories c,
        product_extra_categories e
    where
        p.product_id = c.product_id and
        p.product_id = e.product_id
    ;

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,202
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Sunfighter: That won't work if you has some products and/or categories that do *NOT* have any matching product_extra_categories.

    He needs a LEFT JOIN in there in that case.
    Code:
    select
        p.product_id
        p.product_name,
        c.category_name,
        e.category_name
    from
        products AS p 
        INNER JOIN categories AS C ON p.product_id = c.product_id
        LEFT JOIN product_extra_categories AS e ON p.product_id = e.product_id
    ;
    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.

  • #4
    New Coder
    Join Date
    Mar 2004
    Posts
    95
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks for your help.

    I still can't get it to work using the INNER JOIN and LEFT JOIN. To make it a bit clearer my tables are set up like this, I trimmed excess fields out.
    Code:
    CREATE TABLE IF NOT EXISTS `products` (
      `id` int(16) NOT NULL auto_increment,
      `category_id` mediumint(9) default NULL,
      `name` varchar(255) default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
    
    CREATE TABLE IF NOT EXISTS `product_categories` (
      `id` smallint(6) NOT NULL auto_increment,
      `name` varchar(255) NOT NULL default '',
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=98 ;
    
    CREATE TABLE IF NOT EXISTS `products_extra_categories` (
      `id` int(16) NOT NULL auto_increment,
      `product_id` int(16) NOT NULL,
      `category_id` int(16) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
    So a product is linked to it's primary category as products.category_id = product_categories.id and if there are any extra categories then products_extra_categories.product_id = products.id and products_extra_categories.category_id = product_categories.id. A product can have multiple extra categories.

    With the query, I specify the category id, using 3 in this example. At the moment I have the query as:-
    Code:
    SELECT 
        p.id, 
        c.name 
    FROM 
        products AS p 
        INNER JOIN product_categories AS c ON p.category_id = 3 AND p.category_id = c.id 
        LEFT JOIN products_extra_categories AS e ON p.id = e.product_id AND c.id = e.category_id 
    ORDER BY p.name
    It is still only returning the product with category_id = 3 and not returning the products_extra_categories entry which has category_id 3 and product_id 7.

    I hope this explains it better.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,202
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Oh, I see! Very clever!

    One comment to start with: The products_extra_categories.id field is completely unnecessary and might even be considered a mistake.

    A better way to declare that table would be:
    Code:
    CREATE TABLE IF NOT EXISTS products_extra_categories (
      product_id int NOT NULL,
      category_id int NOT NULL,
      PRIMARY KEY  (product_id, category_id)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
    This also prevents you from duplicating any product_id/category_id combinations!

    ***************************************************

    So... what you want is a tad more complex.

    And it depends on whether you will ever have more than one extra category for a given product. And it depends, then, on whether you want to get all the extra categories back in a single record or you want to see them one per record.

    First answer, which works for *EITHER* no more than one extra category per product *OR* if there are multiple extra categories then you want to see one per result record:
    Code:
    SELECT 
        p.id, 
        p.name AS productName
        c1.name,
        c2.name AS extraCategory
    FROM 
        products AS p 
        INNER JOIN product_categories AS c1 ON p.category_id = c1.id 
        LEFT JOIN products_extra_categories AS e ON p.id = e.product_id 
        LEFT JOIN product_categories AS c2 ON e.category_id = c.id
    WHERE  p.category_id = 3 
    ORDER BY c1.name, p.name, extraCategory
    Do you understand that? You must join *TWICE* to the product_categories table. Once to get the primary category name and once to get the extra category(ies) name(s).

    ***************************************************

    This second answer is in case you want to get only one result record per product.
    Code:
    SELECT 
        p.id, 
        p.name AS productName
        c1.name,
        IFNULL( GROUP_CONCAT( c2.name ), '--none--' ) AS extraCategories
    FROM 
        products AS p 
        INNER JOIN product_categories AS c1 ON p.category_id = c1.id 
        LEFT JOIN products_extra_categories AS e ON p.id = e.product_id 
        LEFT JOIN product_categories AS c2 ON e.category_id = c.id
    WHERE  p.category_id = 3 
    GROUP BY p.id, p.name, c1.name
    ORDER BY c1.name, p.name
    ***************************************************

    By the way: A piece of advice (which you don't have to take, but think hard about it): To avoid confusion, I *NEVER* use a field name of just ID. And I *ALWAYS* try to have primary and foreign key field names the same. I also try not to just use "name" as a field name, so that I don't have to alias the field when I use "name" from two different tables.

    So I would do:
    Code:
    CREATE TABLE IF NOT EXISTS products (
      product_id int(16) NOT NULL auto_increment PRIMARY KEY,
      category_id smallint default NULL, /* same data type as in categories table!!! */
      product_name varchar(255) default NULL
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
    
    CREATE TABLE IF NOT EXISTS product_categories (
      category_id smallint NOT NULL auto_increment PRIMARY KEY,
      category_name varchar(255) NOT NULL default ''
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=98 ;
    Putting PRIMARY KEY after a single field name, as above, is just a shorthand way of declaring the primary key. Makes no difference in any queries, etc.
    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.

  • Users who have thanked Old Pedant for this post:

    Taipan (02-19-2013)

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,202
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    You *might* think about rebuilding that set of tables.

    As given, there is nothing to prevent you from accidentally assigning an "extra category" to a product that is actually the same as the primary category.

    You might think about this design:
    Code:
    CREATE TABLE IF NOT EXISTS products (
      product_id int(16) NOT NULL auto_increment PRIMARY KEY,
      product_name varchar(255) default NULL
      /* notice no category_id! */
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
    
    CREATE TABLE IF NOT EXISTS product_categories (
      category_id smallint NOT NULL auto_increment PRIMARY KEY,
      category_name varchar(255) NOT NULL default ''
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=98 ;
    
    CREATE TABLE IF NOT EXISTS products_extra_categories (
      product_id int NOT NULL,
      category_id smallint NOT NULL,
      is_extra_category boolean NOT NULL default 0,
      PRIMARY KEY  (product_id, category_id)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
    And now you will never get a duplicated set of product/category, either primary or extra.

    And now the query (showing the second one) becomes:
    Code:
    SELECT 
        p.product_name AS productName
        c1.category_name,
        IFNULL( GROUP_CONCAT( c2.category_name ), '--none--' ) AS extraCategories
    FROM 
        products AS p 
        INNER JOIN products_extra_categories AS e1 
                ON product.id = e1.product_id AND e1.is_extra_category = 0
        INNER JOIN product_categories AS c1 
                ON e1.category_id = c1.category_id
        LEFT JOIN products_extra_categories AS e2 
                ON product.id = e1.product_id AND e1.is_extra_category = 1
        LEFT JOIN product_categories AS c2 ON e2.category_id = c2.category_id
    WHERE  p.category_id = 3 
    GROUP BY p.product_name, c1.category_name
    ORDER BY c1.category_name, p.product_name
    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.

  • #7
    New Coder
    Join Date
    Mar 2004
    Posts
    95
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Oh, I see! Very clever!

    One comment to start with: The products_extra_categories.id field is completely unnecessary and might even be considered a mistake.

    A better way to declare that table would be:
    Code:
    CREATE TABLE IF NOT EXISTS products_extra_categories (
      product_id int NOT NULL,
      category_id int NOT NULL,
      PRIMARY KEY  (product_id, category_id)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
    This also prevents you from duplicating any product_id/category_id combinations!
    Thanks for the advice on that. I used the id to make deleting it easier, but your way does look better.

    And also appreciate the advice on the field names for id and name etc. I will start to use these more unique names in future.

    I tried both of your other queries and I am still not getting the full list. Sorry if my explanations are poor.

    All I really need to retrieve is a list of product id's. I am just grabbing the category name while I am getting them. So I know the category id that I want the list of products from (3 for this example), getting any products whose products.category_id = 3 is simple. But the products whose values are in the products_extra_categories will have a different category_id so I think the 'WHERE p.category_id = 3' is making it only return products with that category id.

    What I need to get is a list of product id's where products.category_id = 3 and include the product_id's of any entries in products_extra_categories where products_extra_categories.category_id = 3.

    I am confusing myself just trying to explain it. Maybe I have gone about the whole extra_category approach the wrong way.

    I know I can get them using 2 queries, one on products and the other on products_extra_categories but I want the result to be ordered by the product name. Really appreciate the help.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,202
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Shoot, then you are making it *WAY* too hard!

    Code:
    SELECT 
        id
    FROM 
        products 
        WHERE category_id = 3
        OR product_id IN ( 
            SELECT product_id 
            FROM products_extra_categories 
            WHERE category_id = 3 )
    ORDER BY id
    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.

  • Users who have thanked Old Pedant for this post:

    Taipan (02-19-2013)

  • #9
    New Coder
    Join Date
    Mar 2004
    Posts
    95
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Shoot, then you are making it *WAY* too hard!

    Code:
    SELECT 
        id
    FROM 
        products 
        WHERE category_id = 3
        OR product_id IN ( 
            SELECT product_id 
            FROM products_extra_categories 
            WHERE category_id = 3 )
    ORDER BY id
    Thank you so much, so easy in hind sight

  • #10
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,100
    Thanks
    23
    Thanked 594 Times in 593 Posts
    Something that looked so simple becomes a complicated operation and then thined out to become simple.
    I just love watching the Supreme Master Coder at work.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,202
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Quote Originally Posted by ikuvae22 View Post
    If you use Joomla or Wordpress then you can use xampp to make a database. It is very helpful for you.
    Just create a table select a name, like product. You can type into table product id, product name and product extra categories id as you want.
    If you keep making irrelevant posts like this, you are going to get bad reputation points.
    Last edited by Old Pedant; 02-23-2013 at 11:22 PM.
    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.


  •  

    Posting Permissions

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