CodingForums.com - Help with Multiple Joins

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Help with Multiple Joins (http://www.codingforums.com/showthread.php?t=287814)

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.

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

;

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

;

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.

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.

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

Quote:

Originally Posted by Old Pedant (Post 1314293)

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.

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

Quote:

Originally Posted by Old Pedant (Post 1314306)

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 :thumbsup:

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.

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.

Quote:

Originally Posted by ikuvae22 (Post 1315436)

If you keep making irrelevant posts like this, you are going to get bad reputation points.