SLbvFXXG

1A Complex MySQL Query
2CREATE TABLE IF NOT EXISTS `branches` (
3  `branch_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
4  `merchant_id` int(11) unsigned NOT NULL DEFAULT '0',
5  `place_id` smallint(5) unsigned NOT NULL DEFAULT '0',
6  `branch` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
7  `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
8  `postcode` varchar(6) COLLATE utf8_unicode_ci DEFAULT '',
9  `phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
10  `fax` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
11  `lat` float(10,6) DEFAULT NULL,
12  `lng` float(10,6) DEFAULT NULL,
13  `status` tinyint(4) unsigned NOT NULL DEFAULT '1',
14  PRIMARY KEY (`branch_id`),
15  KEY `lat` (`lat`),
16  KEY `lng` (`lng`)
17) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
18
19CREATE TABLE IF NOT EXISTS `coupons` (
20  `coupon_id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
21  `category_id` tinyint(4) unsigned NOT NULL DEFAULT '0',
22  `merchant_id` mediumint(9) unsigned NOT NULL DEFAULT '0',
23  `coupon` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
24  `description` longtext COLLATE utf8_unicode_ci,
25  `start_date` date DEFAULT NULL,
26  `end_date` date DEFAULT NULL,
27  `coupon_usage` int(10) unsigned NOT NULL DEFAULT '0',
28  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
29  `status` enum('active','passive','deleted','preview') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
30  PRIMARY KEY (`coupon_id`),
31  KEY `start_date` (`start_date`),
32  KEY `end_date` (`end_date`),
33  KEY `merchant_id` (`merchant_id`),
34  KEY `category_id` (`category_id`),
35  KEY `status` (`status`),
36  KEY `created` (`created`),
37  FULLTEXT KEY `description` (`description`)
38) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
39
40CREATE TABLE IF NOT EXISTS `coupons_branches` (
41  `branch_id` int(11) unsigned NOT NULL DEFAULT '0',
42  `coupon_id` int(11) unsigned NOT NULL DEFAULT '0',
43  PRIMARY KEY (`branch_id`,`coupon_id`)
44) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
45
46CREATE TABLE IF NOT EXISTS `merchants` (
47  `merchant_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
48  `merchant` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
49  `website` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
50  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
51  `status` enum('active','passive','deleted') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'passive',
52  PRIMARY KEY (`merchant_id`),
53  KEY `status` (`status`)
54) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
55
56CREATE TABLE IF NOT EXISTS `places` (
57  `place_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
58  `city_id` smallint(5) unsigned NOT NULL DEFAULT '0',
59  `place` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
60  PRIMARY KEY (`place_id`),
61  KEY `place` (`place`),
62  KEY `city_id` (`city_id`)
63) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
64	
65SELECT * FROM (SELECT coupons.coupon_id AS c_id, coupons.category_id, coupons.coupon, merchants.merchant_id, merchants.merchant, (((acos(sin((41.02287686 * pi() / 180)) * sin((branches.lat * pi() / 180)) + cos((41.02287686 * pi() / 180)) * cos((branches.lat * pi()/ 180)) * cos(((29.04632806 - branches.lng) * pi() / 180)))) * 180 / pi()) * 60 * 1.1515 * 1.609344) as distance, COUNT(coupons.coupon_id) AS total_coupons
66FROM (`coupons`)
67INNER JOIN `coupons_branches` ON `coupons`.`coupon_id` = `coupons_branches`.`coupon_id`
68RIGHT OUTER JOIN `branches` ON `coupons_branches`.`branch_id` = `branches`.`branch_id`
69LEFT OUTER JOIN `merchants` ON `coupons`.`merchant_id` = `merchants`.`merchant_id`
70WHERE `coupons`.`status` =  'active'
71AND `merchants`.`status` =  'active'
72GROUP BY `merchants`.`merchant_id`
73HAVING `distance` <= 25000
74ORDER BY `merchants`.`merchant`) as T2 GROUP BY c_id ORDER BY merchant
75	
76SELECT * FROM (SELECT coupons.coupon_id, coupons.coupon_id AS c_id, coupons.category_id, coupons.coupon, merchants.merchant_id, merchants.merchant, coupons.coupon_usage, COUNT(coupons.coupon_id) AS total_coupons
77FROM (`coupons`)
78LEFT OUTER JOIN `merchants` ON `coupons`.`merchant_id` = `merchants`.`merchant_id`
79WHERE `coupons`.`status` =  'active'
80AND `merchants`.`status` =  'active'
81GROUP BY `merchants`.`merchant_id`
82ORDER BY `merchants`.`merchant`) as T2 GROUP BY c_id ORDER BY merchant