Thanks Jim, This outputs 2 results. Is there a way to get the one result set per product regardless of the number of categories associated with the product, yet displaying all the categories associated with said prodcut?
Blessed Be Phillip If you try to protect idiots from themselves, even if you succeed, you just wind up filling the world with idiots. - - Doug Casey On Mon, Jul 12, 2010 at 12:21 PM, Jim Lucas <li...@cmsws.com> wrote: > Phillip Baker wrote: > > Hey All, > > > > I am looking for a little MySQL Query help as well. > > I am not getting any response form the MySQl Email list to my query. > > And knowing there are some SQL wizards on this list I thought I would as > for > > help as well. > > > > I have one table with products that many have one or more categories. > > I am using an index table and am having trouble getting a proper result > set. > > > > Table 1 > > Product_id | Product_Name > > 1 | Product A > > 2 | Product B > > 3 | Product C > > > > Table 2 > > Category_id | Category_Name > > 1 | Admin > > 2 | Marketing > > 3 | Support > > 4 | IT > > > > > > Table 3 > > Product_id | Category_id > > 1 | 1 > > 1 | 3 > > 2 | 2 > > 3 | 3 > > 3 | 4 > > > > Result would look like > > Product A, Admin, Support > > Product B, Marketing > > Product C, Support, IT > > > > I believe this is a one to many using an index table? > > I appreciate any help. > > Thanks. > > > > Blessed Be > > > > Phillip > > > > If you try to protect idiots from themselves, even if you succeed, you > just > > wind up filling the world with idiots. > > - - Doug Casey > > > > > SELECT > products.Product_Name, > categories.Category_Name > FROM > products, > categories, > p2c_map > WHERE > products.Product_ID = p2c_map.Product_ID > AND > categories.Category_ID = p2c_map.Category_ID > > Gives the results that you are looking for. Once you get the data, you > must > concat things your self, but it is everything that you are looking for. > > To search for Categories of a given product, you would add this to the > WHERE section > > AND > products.Product_Name = 'Product A' > > of, if you were looking for all the products in a given category, you would > add this > > AND > categories.Category_Name = 'Category 1' > > > The following is the table structure that I am using with mock data. > > CREATE TABLE IF NOT EXISTS `categories` ( > `Category_ID` int(11) NOT NULL auto_increment, > `Category_Name` varchar(16) collate latin1_bin NOT NULL, > PRIMARY KEY (`Category_ID`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=5 > ; > > INSERT INTO `categories` (`Category_ID`, `Category_Name`) VALUES > (1, 'Category 1'),(2, 'Category 2'),(3, 'Category 3'),(4, 'Category 4'); > > CREATE TABLE IF NOT EXISTS `p2c_map` ( > `Product_id` int(11) NOT NULL, > `Category_ID` int(11) NOT NULL, > PRIMARY KEY (`Product_id`,`Category_ID`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin; > > INSERT INTO `p2c_map` (`Product_id`, `Category_ID`) VALUES > (1, 1),(1, 2),(1, 4),(2, 2),(2, 3),(3, 4),(4, 1),(4, 4); > > CREATE TABLE IF NOT EXISTS `products` ( > `Product_ID` int(11) NOT NULL auto_increment, > `Product_Name` varchar(16) collate latin1_bin NOT NULL, > PRIMARY KEY (`Product_ID`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=5 > ; > > INSERT INTO `products` (`Product_ID`, `Product_Name`) VALUES > (1, 'Product A'),(2, 'Product B'),(3, 'Product C'),(4, 'Product D'); > > -- > Jim Lucas > > A: Maybe because some people are too annoyed by top-posting. > Q: Why do I not get an answer to my question(s)? > A: Because it messes up the order in which people normally read text. > Q: Why is top-posting such a bad thing? >
