sub query
CREATE TABLE IF NOT EXISTS `discount` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT,
`vendor_id` int(11) NOT NULL,
`discount_type` tinyint(4) NOT NULL,
`x` int(11) NOT NULL,
`y` int(11) NOT NULL,
`font_size` int(11) NOT NULL,
`discount_percentage` tinyint(4) NOT NULL,
`start_date` date NOT NULL,
`expiry_date` date NOT NULL,
`description` text NOT NULL,
`no_of_discount` int(10) NOT NULL,
`discount_picture` text NOT NULL,
`category` int(11) NOT NULL,
`product_name` varchar(50) NOT NULL,
`product_pdf` text NOT NULL,
`product_picture` text NOT NULL,
`country` int(11) NOT NULL,
`state` int(11) NOT NULL,
`city` varchar(50) NOT NULL,
`status` int(1) NOT NULL,
`created_datetime` date NOT NULL,
`discount_name` varchar(50) NOT NULL,
`limits` int(11) NOT NULL,
`brand_name` varchar(20) NOT NULL,
`coupon_type` varchar(20) NOT NULL,
`coupon_code` varchar(50) NOT NULL,
`views` tinyint(4) NOT NULL,
`random_id` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
)
INSERT INTO `discount` (`id`, `vendor_id`, `discount_type`, `x`, `y`, `font_size`, `discount_percentage`, `start_date`, `expiry_date`, `description`, `no_of_discount`, `discount_picture`, `category`, `product_name`, `product_pdf`, `product_picture`, `country`, `state`, `city`, `status`, `created_datetime`, `discount_name`, `limits`, `brand_name`, `coupon_type`, `coupon_code`, `views`, `random_id`) VALUES (1, 2, 1, 5, 5, 5, 67, '2010-06-22', '2010-06-30', 'test', 5,
'1277875213_10_discount.jpg', 1, 'new product', '1277875213_product_roadmap.pdf',
'1277875213_Samsung.jpg', 13, 70, 'test', 1, '2010-06-30', 'test', 0, '', '1', '', 3, 'xnqvrcmb3ft5s7pw'), (2, 2, 2, 100, 100, 100, 67, '0000-00-00', '0000-00-00', 'test test', 0, '1277875439_10_discount.jpg', 2, 'new', '1277875439_product_roadmap.pdf', '1277875439_blue.jpg', 44, 141, 'test', 1, '2010-06-30', 'new new', 50, 'latest', '1', '_test_50', 0, 'h0yp1v7m9csw6jb5'),
(3, 2, 2, 10, 5, 5, 0, '0000-00-00', '0000-00-00', 'test', 0, '1277876878_10_discount.jpg', 1, 'new', '1277876878_product_roadmap.pdf', '1277876878_linda.jpg', 13, 71, 'test', 1, '2010-06-30', 'new new', 50, 'latest',
'2', 'Coke_NewYork_001 ', 3, '3xcrwfp4gy2h90q8');
CREATE TABLE IF NOT EXISTS `rating` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`type` tinyint(4) NOT NULL,
`relation_id` int(11) NOT NULL,
`rating` tinyint(4) NOT NULL,
`created_datetime` datetime NOT NULL,
`modified_datetime` datetime NOT NULL,
PRIMARY KEY (`id`)
)
INSERT INTO `rating` (`id`, `customer_id`, `type`, `relation_id`, `rating`,
`created_datetime`, `modified_datetime`) VALUES
(1, 1, 1, 1, 5, '2010-06-19 04:32:17', '2010-06-19 04:32:28'),
(2, 1, 1, 4, 2, '2010-06-19 04:32:43', '2010-06-19 04:32:43'),
(3, 1, 2, 2, 5, '2010-06-19 04:40:58', '2010-06-22 05:17:58'),
(4, 1, 2, 6, 2, '2010-06-19 04:41:11', '2010-06-22 05:18:21'),
(5, 1, 2, 3, 3, '2010-06-19 04:41:52', '2010-06-19 04:41:52');
CREATE TABLE IF NOT EXISTS `favorites` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`type` tinyint(4) NOT NULL,
`relation_id` int(11) NOT NULL,
`created_datetime` datetime NOT NULL,
PRIMARY KEY (`id`)
)
INSERT INTO `favorites` (`id`, `customer_id`, `type`, `relation_id`,
`created_datetime`) VALUES
(3, 1, 2, 3, '2010-06-18 12:52:46'),
(5, 1, 2, 6, '2010-06-18 13:03:33'),
(15, 1, 1, 4, '2010-06-22 07:08:34'),
(13, 1, 2, 8, '2010-06-22 05:17:46');
SELECT a.id, a.random_id, a.vendor_id, a.views, a.description, a.discount_name,
a.discount_picture, b.rating, b.type as rating_type, c.id as fav_id FROM `discount` as a LEFT JOIN `rating` as b ON(a.id = b.relation_id AND b.type = '1') LEFT JOIN favorites as c ON(a.id = c.relation_id AND c.type = '1' AND c.customer_id = '".$_SESSION['customer_session']['user_id']."') WHERE 1 = 1;
In the discount table i have the start_date and expiry_date for discount_type = 1
only not discount_type = 2.
But now i want to display all the records and I want to check the expiry date with today date where discount_type = '1' only.
Shall i use subquery or union.......
Anyone can help me out...........