======Adjacency Tree: Undokumentierter Codeschnipsel======

Das All-In_one-Wunder
<code sql>SELECT
	@prev_node_id AS previd,
	desc_tree.nextid,
	@prev_node_id := desc_tree.id AS id,
	node.title,
	desc_tree.tree_level,
	desc_tree.have_children,
	desc_tree.id_path,
	desc_tree.title_path,
   CONCAT(REPEAT('--', desc_tree.tree_level-1), desc_tree.id) AS tree_id_string,
   CONCAT(REPEAT('--', desc_tree.tree_level-1), node.title) AS tree_title_string
FROM
	(SELECT @prev_node_id := NULL) AS vars3,
	-- Das ganze verkehrt sortieren um die nächste ID zu ermitteln
	(
		SELECT
			@next_node_id AS nextid,
			@next_node_id := unsorted_tree.id AS id,
			unsorted_tree.tree_level,
			unsorted_tree.have_children,
			unsorted_tree.id_path,
			unsorted_tree.title_path,
			unsorted_tree.tree_sort
		FROM
			(SELECT @next_node_id := NULL) AS vars2,
			(
				SELECT
					t.id,
					GROUP_CONCAT(pathid ORDER BY rownum DESC SEPARATOR '>') AS id_path,
					GROUP_CONCAT(LPAD(pathid, 4, 0) ORDER BY rownum DESC SEPARATOR '.') AS tree_sort,
					GROUP_CONCAT(title ORDER BY rownum DESC SEPARATOR ' -> ') AS title_path,
					COUNT(DISTINCT pathid) AS tree_level,
					(NOT parent.id IS NULL) AS have_children
				FROM
					(
						SELECT
							# Zeilennummer. Wird später für die Sortierung des GROUP_CONCAT verwednet
							@rownum := @rownum+1 AS rownum,
							# id die für den Pfad verwendet wird
							IF(@lastid <> mylist.id, @id := mylist.id, @id) AS pathid,
							# Die Start-Id.
							@lastid := mylist.id AS id,
							# bestimmen der nächsten id im Path
							@id := (SELECT parentID FROM nav  WHERE id = @id) AS parentID
						FROM
							# Variablen initialisieren
							(SELECT @id := 0, @lastid := 0, @rownum := 0) AS vars,
							# Die Tabelle mit sich selber multiplizieren umd genügend
							# Zeilen zur Verfügung zu haben
							(SELECT id FROM nav) AS myloop,
							(SELECT id FROM nav) AS mylist
						ORDER BY
							mylist.id,
							myloop.id				
					) AS t
					-- Mit dem INNER join filtern wir auch gleich alle 'gültigen' Kombinationen aus
					INNER JOIN nav
						ON t.pathid = nav.id
					-- Flag ob der node ein Perent ist
					LEFT JOIN (SELECT DISTINCT parentid AS id FROM nav WHERE NOT parentid IS NULL) AS parent
						ON parent.id = t.id
				WHERE
					pathid IS NOT NULL
				GROUP BY
					id
			) AS unsorted_tree
		ORDER BY 
			tree_sort DESC
	) AS desc_tree
	INNER JOIN nav AS node
		ON node.id = desc_tree.id
ORDER BY 
	tree_sort</code>

^ previd ^ nextid ^ id ^ title ^ tree_level ^ have_children ^ id_path ^ title_path ^ tree_id_string ^ tree_title_string ^
| _NULL_ | 2 | 1 | Node #001 | 1 | 1 | 1 | Node #001 | 1 | Node #001 |
| 1 | 3 | 2 | Node #002 | 2 | 1 | 1>2 | Node #001 -> Node #002 | 2 | Node #002 |
| 2 | 8 | 3 | Node #003 | 3 | 0 | 1>2>3 | Node #001 -> Node #002 -> Node #003 | 3 | Node #003 |
| 3 | 10 | 8 | Node #008 | 3 | 1 | 1>2>8 | Node #001 -> Node #002 -> Node #008 | 8 | Node #008 |
| 8 | 9 | 10 | Node #010 | 4 | 1 | 1>2>8>10 | Node #001 -> Node #002 -> Node #008 -> Node #010 | 10 | Node #010 |
| 10 | 4 | 9 | Node #009 | 5 | 0 | 1>2>8>10>9 | Node #001 -> Node #002 -> Node #008 -> Node #010 -> Node #009 | 9 | Node #009 |
| 9 | 5 | 4 | Node #004 | 2 | 1 | 1>4 | Node #001 -> Node #004 | 4 | Node #004 |
| 4 | 6 | 5 | Node #005 | 3 | 1 | 1>4>5 | Node #001 -> Node #004 -> Node #005 | 5 | Node #005 |
| 5 | 7 | 6 | Node #006 | 4 | 1 | 1>4>5>6 | Node #001 -> Node #004 -> Node #005 -> Node #006 | 6 | Node #006 |
| 6 | 11 | 7 | Node #007 | 5 | 0 | 1>4>5>6>7 | Node #001 -> Node #004 -> Node #005 -> Node #006 -> Node #007 | 7 | Node #007 |
| 7 | 12 | 11 | Node #011 | 1 | 1 | 11 | Node #011 | 11 | Node #011 |
| 11 | 13 | 12 | Node #012 | 2 | 0 | 11>12 | Node #011 -> Node #012 | 12 | Node #012 |
| 12 | _NULL_ | 13 | Node #013 | 2 | 0 | 11>13 | Node #011 -> Node #013 | 13 | Node #013 |
{{tag>MySQL Tree}}