...

View Full Version : Help with Multiple Joins



Taipan
02-18-2013, 06:10 AM
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.

sunfighter
02-18-2013, 04:13 PM
I don't think
AND product_extra_categories.category_id = categories.id is necessary, but don't think it hurts anything either.


WHERE products.id = categories.product_id is necessary.

Something like this (you don't give us the column names so I guess):


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
;

Old Pedant
02-18-2013, 04:41 PM
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.


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


;

Taipan
02-18-2013, 11:56 PM
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.

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


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.

Old Pedant
02-19-2013, 01:59 AM
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:


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:


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.


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:


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.

Old Pedant
02-19-2013, 02:09 AM
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:


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:


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

Taipan
02-19-2013, 03:02 AM
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:


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.

Old Pedant
02-19-2013, 03:32 AM
Shoot, then you are making it *WAY* too hard!



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

Taipan
02-19-2013, 05:44 AM
Shoot, then you are making it *WAY* too hard!



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:

sunfighter
02-19-2013, 03:18 PM
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.

Old Pedant
02-23-2013, 11:16 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum