This one has me pretty stumped at the moment. I've tried variations
with MySQL IF() statements but can't quite get it right.
How can I select all of the records of the table 'items' that are
active. Restricted such that if there are items active for the
subcategory and the parent category *only* the items of the
subcategory get returned and if there are only items active for the
parent category they get returned. The parent category will always
have active items, but the subcategory may not.
Given the following schema (subset of the the real tables naturally):
#
# Table structure for table `categories`
#
CREATE TABLE `categories` (
`cat_id` int(11) NOT NULL auto_increment,
`parent_id` int(11) NOT NULL default '0',
`cat_name` varchar(10) NOT NULL default '',
PRIMARY KEY (`cat_id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
#
# Dumping data for table `categories`
#
INSERT INTO `categories` VALUES (1, 0, 'parent');
INSERT INTO `categories` VALUES (2, 1, 'child');
# --------------------------------------------------------
#
# Table structure for table `items`
#
CREATE TABLE `items` (
`item_id` int(11) NOT NULL auto_increment,
`cat_id` int(11) NOT NULL default '0',
`item_text` varchar(10) NOT NULL default '',
`active` int(11) NOT NULL default '1',
PRIMARY KEY (`item_id`),
KEY `category_id` (`cat_id`)
) TYPE=MyISAM AUTO_INCREMENT=14 ;
#
# Dumping data for table `items`
#
INSERT INTO `items` VALUES (1, 1, 'parent it', 1);
INSERT INTO `items` VALUES (2, 1, 'parent it', 1);
INSERT INTO `items` VALUES (3, 1, 'parent it', 1);
INSERT INTO `items` VALUES (4, 1, 'parent it', 1);
INSERT INTO `items` VALUES (5, 1, 'parent it', 1);
INSERT INTO `items` VALUES (10, 2, 'child_it', 1);
INSERT INTO `items` VALUES (11, 2, 'child_it', 1);
INSERT INTO `items` VALUES (12, 2, 'child_it', 1);
INSERT INTO `items` VALUES (13, 2, 'child_it', 1);
Any help would be appreciated!