ZQu0Y0ys

1category_id
2category_name
3category_parent
4category_order 
5     
6parent category 1
7sub category 1
8sub category 2
9parent category 2
10sub category 1
11sub category 2
12sub category 3
13parent category 3
14sub category 1 
15     
16SELECT
17fcat.id fcat_id,
18fcat.name fcat_name,
19fcat.order fcat_order,
20fcat.parent fcat_parent,
21fsub.id fsub_id,
22fsub.name fsub_name,
23fsub.order fsub_order,
24fsub.parent fsub_parent
25FROM forum_categories AS fcat
26LEFT OUTER JOIN forum_categories AS fsub ON fcat.id = fsub.parent
27ORDER BY ISNULL(fcat.order) ASC, fcat.id ASC, ISNULL(fsub.order) ASC, fsub.id ASC 
28     
29select c1.*, 
30       c2.*,
31       if (c2.category_parent is NULL, "parent category", "sub category") as text
32from cat c1 left join cat c2 
33    on c1.category_id = c2.category_parent
34order by c1.category_id, c2.category_id 
35     
36select * from (
37  select c.*,
38  coalesce(nullif(c.parent, 0), c.id) as groupID,
39  case when c.parent = 0 then 1 else 0 end as isparent,
40  case when p.`order` = 0 then c.id end as orderbyint
41  from category c
42  left join category p on p.id = c.parent
43) c order by groupID, isparent desc, orderbyint, name 
44     
45SELECT c.*,
46  CASE WHEN isnull(is_par.cat_id) THEN c.cat_sort_order ELSE is_par.cat_sort_order END as sort_order,
47  CASE WHEN isnull(is_par.cat_id) THEN c.cat_id ELSE is_par.cat_id END as catid ,
48  CASE WHEN isnull(is_par.cat_id) THEN 0 ELSE c.cat_sort_order END as subcat_order
49FROM `category` c
50LEFT JOIN `category` is_par ON is_par.cat_id = c.cat_parent_id
51ORDER BY  sort_order , catid, subcat_order 
52     
53SELECT c.cat_id as ccatid,c.cat_sort_order as ccatsortorder, is_par.cat_id as isparcatid,is_par.cat_parent_id as ispar_catparentid,is_par.cat_sort_order as isparcatsortorder,
54  CASE WHEN isnull(is_par.cat_id) THEN c.cat_sort_order ELSE is_par.cat_sort_order END as sort_order,
55  CASE WHEN isnull(is_par.cat_id) THEN c.cat_id ELSE is_par.cat_id END as catid,
56  CASE WHEN isnull(is_par.cat_id) THEN 0 ELSE c.cat_sort_order END as subcat_order
57FROM `category` c
58LEFT JOIN `category` is_par ON is_par.cat_id = c.cat_parent_id 
59     
60CREATE TABLE IF NOT EXISTS `categories` (
61  `category_id` int(11) NOT NULL AUTO_INCREMENT,
62  `category_parent` int(11) NOT NULL,
63  `category_name` varchar(100) COLLATE latin1_spanish_ci NOT NULL,
64  PRIMARY KEY (`category_id`)
65) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci; 
66     
67SELECT category_id,category_parent,category_name,
68(CASE
69    WHEN category_parent=category_id OR category_parent=0  THEN category_id
70    WHEN category_parent<>category_id THEN category_parent
71END) AS 'the_parent'
72FROM categories ORDER BY the_parent ASC, category_parent ASC, category_name ASC 
73     
74SELECT
75    main.id AS main_id,
76    level1.id AS level1_id,
77    level2.id AS level2_id,
78    level3.id AS level3_id
79FROM
80    categories AS main
81LEFT OUTER JOIN categories AS level1 ON level1.parent_id = main.id
82LEFT OUTER JOIN categories AS level2 ON level2.parent_id = level1.id
83LEFT OUTER JOIN categories AS level3 ON level3.parent_id = level2.id
84WHERE
85    main.parent_id = "ID-OF-MAIN-CATEGORY"
86ORDER BY
87    main_id,
88    level1_id,
89    level2_id,
90    level3_id 
91     
92select *, if (parent_category_id is NULL, id, concat(parent_category_id, id)) as o
93from category
94order by o