469,090 Members | 1,203 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

MySQL Query help

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!
Jul 19 '05 #1
0 985

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

51 posts views Thread by w_curtis | last post: by
reply views Thread by Mike Chirico | last post: by
2 posts views Thread by pratchaya | last post: by
39 posts views Thread by Mairhtin O'Feannag | last post: by
3 posts views Thread by Juan Antonio Villa | last post: by
10 posts views Thread by Caffeneide | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.