query problem
Hello all,
I am tring to execute this condition based query from two table(product & itemdetails),
where,
if prod_type from itemdetails is "exclusive"
then
prod_status from product table is inactive
else
prod_status from product table is active.
then
This is my table structure:::::::::::::::
CREATE TABLE `itemdetails` (
`sno` int(11) NOT NULL auto_increment,
`cart_id` varchar(60) default NULL,
`prod_id` varchar(30) default NULL,
`prod_price` double(5,0) default NULL,
`prod_desc` varchar(250) default NULL,
`prod_quantity` int(2) default NULL,
`prod_type` varchar(20) default NULL,
`prod_source` varchar(30) default NULL,
`prod_colour` varchar(30) NOT NULL default '',
`prod_colno` int(10) NOT NULL default '0',
PRIMARY KEY (`sno`)
) ENGINE=MyISAM AUTO_INCREMENT=139 DEFAULT CHARSET=latin1;
/*Data for the table `itemdetails` */
insert into `itemdetails` values
(82,'ef253b43f5cbd61142e71b90de6cc327','INF001',45 00,'test',1,'Non-exclusive','.psd,.html','INF001BL',1),
(81,'ef253b43f5cbd61142e71b90de6cc327','HOL001',60 00,'description',1,'Non-exclusive','.psd,.html','HOL001RL',3),
(80,'ef253b43f5cbd61142e71b90de6cc327','BUS001',50 0,'nothing ',1,'Non-exclusive','.psd,.html','BUS001BL',1),
(61,'6bdf65b5714a7bf7f997f51c52d92805','BUS001',10 00,'nothing ',1,'Exclusive','.psd,.html','BUS001GL',2),
(62,'6bdf65b5714a7bf7f997f51c52d92805','BUS003',12 000,'test',1,'Exclusive','.psd,.html','BUS003BL',1 );
CREATE TABLE `product` (
`sno` int(11) NOT NULL auto_increment,
`prod_id` varchar(20) NOT NULL default '',
`prod_desc` text NOT NULL,
`prod_nonexclusive` double(5,0) default NULL,
`prod_exclusive` double(5,0) NOT NULL default '0',
`prod_colour` varchar(50) NOT NULL default '',
`prod_source` varchar(50) NOT NULL default '',
`prod_date` date NOT NULL default '0000-00-00',
`image1_small` longblob,
`image1_big` longblob,
`image1_small_type` varchar(20) default NULL,
`image1_big_type` varchar(20) default NULL,
`image1_prod_id` varchar(20) NOT NULL default '',
`image2_small` longblob,
`image2_big` longblob,
`image2_small_type` varchar(20) default NULL,
`image2_big_type` varchar(20) default NULL,
`image2_prod_id` varchar(20) NOT NULL default '',
`image3_small` longblob,
`image3_big` longblob,
`image3_small_type` varchar(20) default NULL,
`image3_big_type` varchar(20) default NULL,
`image3_prod_id` varchar(20) NOT NULL default '',
`image4_small` longblob,
`image4_big` longblob,
`image4_small_type` varchar(20) default NULL,
`image4_big_type` varchar(20) default NULL,
`image4_prod_id` varchar(20) NOT NULL default '',
`prod_status` varchar(20) default NULL,
PRIMARY KEY (`sno`,`prod_id`),
UNIQUE KEY `sno` (`sno`),
KEY `prod_colour` (`prod_colour`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Plz help me, urgent.
Regards
puru
shopping
|