Re: Looking for a little MySQL help as well

Jim Lucas wrote: > 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. Forgot to give create where credit is due. I found an example of this method here and munged it to make it work with the example table structure and data examples. http://www.tek-tips.com/viewthread.cfm?qid=1541787&page=15 And I looked here for usage of MySQLs GROUP_CONCAT() http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat Enjoy, Jim > >> 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@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? >>> > > -- 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?