A Complex MySQL Query

Question

I'm trying to write a complex SQL query but couldn't manage to get a result set with correct values.

• coupons table holds some online coupons.
• merchants table holds merchant info and tied to coupons with coupons.merchant_id.
• branches table holds merchant's branches with lat and lng values (to calculate distance and nearest branches etc.) and tied to merchants table with branches.merchant_id.
• coupons_branches table ties coupons and branches tables.
• places table holds some special places, like shopping malls etc. and tied to branches table with branches.place_id.

Below is my table structure;

CREATE TABLE IF NOT EXISTS `branches` (
  `branch_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) unsigned NOT NULL DEFAULT '0',
  `place_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `branch` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `postcode` varchar(6) COLLATE utf8_unicode_ci DEFAULT '',
  `phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fax` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lat` float(10,6) DEFAULT NULL,
  `lng` float(10,6) DEFAULT NULL,
  `status` tinyint(4) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`branch_id`),
  KEY `lat` (`lat`),
  KEY `lng` (`lng`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `coupons` (
  `coupon_id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `merchant_id` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `coupon` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `description` longtext COLLATE utf8_unicode_ci,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `coupon_usage` int(10) unsigned NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','passive','deleted','preview') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  PRIMARY KEY (`coupon_id`),
  KEY `start_date` (`start_date`),
  KEY `end_date` (`end_date`),
  KEY `merchant_id` (`merchant_id`),
  KEY `category_id` (`category_id`),
  KEY `status` (`status`),
  KEY `created` (`created`),
  FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `coupons_branches` (
  `branch_id` int(11) unsigned NOT NULL DEFAULT '0',
  `coupon_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`branch_id`,`coupon_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `merchants` (
  `merchant_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `merchant` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `website` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','passive','deleted') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'passive',
  PRIMARY KEY (`merchant_id`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `places` (
  `place_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `place` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`place_id`),
  KEY `place` (`place`),
  KEY `city_id` (`city_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

And here is my SQL;

SELECT * 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
FROM (`coupons`)
INNER JOIN `coupons_branches` ON `coupons`.`coupon_id` = `coupons_branches`.`coupon_id`
RIGHT OUTER JOIN `branches` ON `coupons_branches`.`branch_id` = `branches`.`branch_id`
LEFT OUTER JOIN `merchants` ON `coupons`.`merchant_id` = `merchants`.`merchant_id`
WHERE `coupons`.`status` =  'active'
AND `merchants`.`status` =  'active'
GROUP BY `merchants`.`merchant_id`
HAVING `distance` <= 25000
ORDER BY `merchants`.`merchant`) as T2 GROUP BY c_id ORDER BY merchant

I'm trying to find out a merchant's total coupons. I want to list only the latest coupon from that merchant. I don't want to list all coupons. I want to group merchants first, show how many coupons that merchant have. Then at another page, I will display coupons belong to that merchant.

Everything works great except total_coupons column. Total coupons column is not calculated correctly. If I write the like below, it calculates correctly.

SELECT * 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
FROM (`coupons`)
LEFT OUTER JOIN `merchants` ON `coupons`.`merchant_id` = `merchants`.`merchant_id`
WHERE `coupons`.`status` =  'active'
AND `merchants`.`status` =  'active'
GROUP BY `merchants`.`merchant_id`
ORDER BY `merchants`.`merchant`) as T2 GROUP BY c_id ORDER BY merchant

There is something wrong with branches and places tables but I couldn't find.

Answer 1

Try by changing the COUNT() expression to COUNT(DISTINCT coupons.coupon_id). Without the DISTINCT keyword, even duplicate coupons are being counted into the total sum of merchant's coupons. I'm guessing you're looking only for the sum of coupons currently distributed to branches.