Re: Looking for a little MySQL help as well

[Jim Lucas <lists@xxxxxxxxx>]

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?

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php