--
--	Tree Structure in MySQL
--	Copyright (C) 2011 Matous J. Fialka, <http://mjf.cz/>
--	Released under the terms of The MIT License
--

--
--	Create tree table structure
--

DROP	TABLE	IF EXISTS	tree
	;

CREATE	TABLE			tree
(
	id	int(11)		NOT NULL	AUTO_INCREMENT	,
	link_1	int(11)		NOT NULL			,
	link_2	int(11)		NOT NULL			,

	PRIMARY	KEY(id)

)	DEFAULT	CHARSET		=		utf8
	ENGINE			=		InnoDB
	;

--
--	Insert example tree table structure data values
--
--				0
--				|
--		--------------- 1 ---------------
--		|				|
--	------- 2 -------		------- 6 -------
--	|	|	|		|	|	|
--	3	4	5		7	9	10
--					|
--					8
--

INSERT	INTO	tree	VALUES
(	1,	1,	20	)	,
(	2,	2,	9	)	,
(	3,	3,	4	)	,
(	4,	5,	6	)	,
(	5,	7,	8	)	,
(	6,	10,	18	)	,
(	7,	11,	14	)	,
(	8,	12,	13	)	,
(	9,	15,	16	)	,
(	10,	17,	18	)
	;

--
--	Print tree table structure data
--

SELECT	CONCAT(REPEAT('  ', COUNT(parent.id) - 1), node.id)
		AS	TREE	FROM	tree	AS node		,
	tree	AS	parent	WHERE	node.link_1
					BETWEEN parent.link_1
					AND	parent.link_2
				GROUP BY	node.id
				ORDER BY	node.link_1
	;

--
--	The output of the select above should be as follows
--
--	TREE
--	1
--	  2
--	    3
--	    4
--	    5
--	  6
--	    7
--	      8
--	    9
--	    10
--