Phillip Baker wrote: > 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? Run this: SELECT p.Product_Name, GROUP_CONCAT(c.Category_Name SEPARATOR ', ') AS Category_Names FROM p2c_map AS pc INNER JOIN products AS p ON p.Product_ID = pc.Product_ID INNER JOIN categories AS c ON FIND_IN_SET(c.Category_ID, pc.Category_ID) > 0 GROUP BY pc.Product_ID When I run this in phpMyAdmin it gives me a blob result, but when you run it from the CLI it will return the correct thing. > > 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 <lists@xxxxxxxxx> 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? >> > -- 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? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
