MySQL Forums
Forum List  »  Optimizer & Parser

sub query
Posted by: lavanya kv
Date: July 01, 2010 05:33AM

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...........

Options: ReplyQuote


Subject
Views
Written By
Posted
sub query
2891
July 01, 2010 05:33AM
1480
July 02, 2010 09:01PM
1408
July 02, 2010 10:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.