E2Zf8VRb

1SELECT `m`.`id`, `m`.`primary_category_id`, `m`.`primary_category_priority`, `m`.`description`
2FROM (`merchant` AS m)
3LEFT JOIN `merchant_category`
4    ON `merchant_category`.`merchant_id` = `m`.`id`
5WHERE
6    `merchant_category`.`category_id` = '2'
7    OR `m`.`primary_category_id` = '2'
8GROUP BY `m`.`id`
9ORDER BY
10    LEAST(merchant_category.priority = 0, `primary_category_priority` = 0) ASC,
11    LEAST(merchant_category.priority, `primary_category_priority` ) ASC
12LIMIT 10
13	
14CREATE TABLE IF NOT EXISTS `merchant` (
15  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
16  `name` varchar(100) CHARACTER SET utf8 NOT NULL,
17  `primary_category_id` int(11) NOT NULL,
18  `primary_category_priority` int(10) unsigned NOT NULL DEFAULT '0',
19  `description` mediumtext CHARACTER SET utf8 NOT NULL,
20  PRIMARY KEY (`id`)
21)
22
23CREATE TABLE IF NOT EXISTS `merchant_category` (
24  `id` int(10) NOT NULL AUTO_INCREMENT,
25  `merchant_id` int(10) NOT NULL,
26  `category_id` int(10) NOT NULL,
27  `priority` int(10) unsigned NOT NULL DEFAULT '0',
28  PRIMARY KEY (`id`)
29)
30	
31CREATE TABLE IF NOT EXISTS `merchant_category` (
32  `id` int(10) NOT NULL AUTO_INCREMENT,
33  `merchant_id` int(10) NOT NULL,
34  `category_id` int(10) NOT NULL,
35  `priority` int(10) unsigned NOT NULL DEFAULT '0',
36  PRIMARY KEY (`id`),
37  CONSTRAINT mc_fk FOREIGN KEY (`merchant_id`) REFERENCES `merchant`(`id`)
38)