Re: Looking for a little MySQL help as well

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@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 (óÁ²•À²F^ô:Cý >> ›²+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?