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/my***********...ie.nctu.edu.tw