Re: Looking for a little MySQL help as well

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?