yKAPbTAS

1CREATE TABLE IF NOT EXISTS `categories_description` (
2  `categories_id` int(11) NOT NULL DEFAULT '0',
3  `language_id` int(11) NOT NULL DEFAULT '1',
4  `categories_name` varchar(128) NOT NULL,
5  `categories_banner_url` varchar(255) DEFAULT NULL,
6  `categories_banner_swf_x` int(11) DEFAULT NULL,
7  `categories_banner_swf_y` int(11) DEFAULT NULL,
8  PRIMARY KEY (`categories_id`,`language_id`),
9  KEY `idx_categories_name` (`categories_name`)
10) ENGINE=MyISAM DEFAULT CHARSET=utf8;
11
12INSERT INTO categories_description VALUES (10, 2, 'test','test',10,10);
13INSERT INTO categories_description VALUES (11, 2, 'test','test',10,10);
14INSERT INTO categories_description VALUES (12, 2, 'test','test',10,10);
15INSERT INTO categories_description VALUES (13, 2, 'test','test',10,10);
16
17CREATE TABLE IF NOT EXISTS `categories` (
18  `categories_id` int(11) NOT NULL AUTO_INCREMENT,
19  `categories_image` varchar(64) DEFAULT NULL,
20  `categories_banner` varchar(64) DEFAULT NULL,
21  `parent_id` int(11) NOT NULL DEFAULT '0',
22  `sort_order` int(3) DEFAULT NULL,
23  `date_added` datetime DEFAULT NULL,
24  `last_modified` datetime DEFAULT NULL,
25  `categories_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
26  PRIMARY KEY (`categories_id`),
27  KEY `idx_categories_parent_id` (`parent_id`)
28) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1258 ;
29
30INSERT INTO categories VALUES (10, 'test', 'test',13,1,'2016-11-01','2016-11-01',1);
31INSERT INTO categories VALUES (13, 'test', 'test',12,1,'2016-11-01','2016-11-01',1);
32INSERT INTO categories VALUES (11, 'test', 'test',12,1,'2016-11-01','2016-11-01',1);
33INSERT INTO categories VALUES (12, 'test', 'test',10,1,'2016-11-01','2016-11-01',1);
34
35
36
37///////////////////////////////////////////////
38//////The query
39
40
41
42SELECT a.categories_id, a.parent_id, b.categories_name FROM  `categories` AS a,  `categories_description` AS b WHERE b.language_id =2 AND a.categories_id = b.categories_id GROUP BY a.parent_id DESC