TASK: I have to generate a report with all categories, subcategories and products in database.
PROBLEM: I want to write one query that will return: 1. category 2. subcategory: determined by parent_id 3. products in each category or subcategory. I have four tables with the following structure: ------------------------------------------------------------------------ --- CREATE TABLE `categories` ( `categories_id` int(11) NOT NULL auto_increment, `parent_id` int(11) NOT NULL default '0', `sort_order` int(3) default NULL, PRIMARY KEY (`categories_id`), ) INSERT INTO `categories` VALUES (1, 0, 1); INSERT INTO `categories` VALUES (2, 1, 1); INSERT INTO `categories` VALUES (3, 0, 2); INSERT INTO `categories` VALUES (4, 3, 1); CREATE TABLE `categories_description` ( `categories_id` int(11) NOT NULL default '0', `categories_name` varchar(32) NOT NULL default '', PRIMARY KEY (`categories_id`), ) INSERT INTO `categories_description` VALUES (1, 'Sedans'); INSERT INTO `categories_description` VALUES (2, 'Honda'); INSERT INTO `categories_description` VALUES (3, 'Luxury'); INSERT INTO `categories_description` VALUES (4, 'Mercedez'); CREATE TABLE `products_to_categories` ( `products_id` int(11) NOT NULL default '0', `categories_id` int(11) NOT NULL default '0', PRIMARY KEY (`products_id`,`categories_id`) ) INSERT INTO `products_to_categories` VALUES (100, 1); INSERT INTO `products_to_categories` VALUES (101, 2); INSERT INTO `products_to_categories` VALUES (102, 4); INSERT INTO `products_to_categories` VALUES (103, 4); CREATE TABLE `products` ( `products_id` int(11) NOT NULL auto_increment, `products_model` varchar(12) default NULL, `products_image` varchar(64) default 'image_na.gif', `products_price` decimal(15,4) NOT NULL default '0.0000', PRIMARY KEY (`products_id`), ) INSERT INTO `products` VALUES (100, 'Civic', 'civic.jpg', '150000'); INSERT INTO `products` VALUES (101, 'Accord', 'accord.jpg', '250000'); INSERT INTO `products` VALUES (102, 'S500', 's500.jpg', '60000'); INSERT INTO `products` VALUES (103, 'S600', 's600.jpg', '90000'); ------------------------------------------------------------------------ --- I can do this with multiple queries, but to be more efficient can I do this with one query? Your help is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
