ich denke, die Lösung 1 ist eine Sackgasse.
Nachdem die Tabellenstruktur komplett umgestellt ist, muss natürlich auch der SQL neu überdacht werden.
Code:
CREATE TABLE IF NOT EXISTS `tree` (
`id` int(11) NOT NULL,
`lft` int(11) NOT NULL,
`rgt` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Daten für Tabelle `tree`
--
INSERT INTO `tree` (`id`, `lft`, `rgt`) VALUES
(1, 1, 16),
(2, 2, 3),
(3, 4, 13),
(4, 5, 12),
(5, 6, 7),
(6, 8, 11),
(7, 9, 10),
(8, 14, 15);
CREATE TABLE IF NOT EXISTS `element` (
`id` int(11) NOT NULL,
`tag` varchar(100) NOT NULL,
`desc` text NOT NULL,
`id_tree` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Daten für Tabelle `element`
--
INSERT INTO `element` (`id`, `tag`, `desc`, `id_tree`) VALUES
(1, 'A', 'content A', 1),
(2, 'B', 'content B', 2),
(3, 'C', 'content C', 3),
(4, 'D', 'content D', 4),
(5, 'E', 'content E', 5),
(6, 'F', 'content F', 6),
(7, 'G', 'content G', 7),
(8, 'H', 'content H', 8),
(9, 'G2', 'content G1', 7),
(10, 'G3', 'content G2', 7),
(11, 'C1', 'content C1', 3);
SELECT CASE r.flag
WHEN 1 THEN CONCAT( '<' , r.tag, '>', r.`desc` )
WHEN 2 THEN CONCAT( '</' , r.tag, '>')
WHEN 3 THEN CONCAT( '<' , r.tag, '>', r.`desc`, '</' , r.tag, '>')
END AS xml_teil
FROM (SELECT e.tag, 1 AS flag, t.lft AS pt, e.`desc`
FROM tree t
JOIN element e
ON e.id_tree = t.id
WHERE 1 = ( SELECT COUNT(*)
FROM element e2
WHERE e2.id_tree = e.id_tree
)
UNION ALL
SELECT e.tag, 2 AS flag, t.rgt AS pt, e.`desc`
FROM tree t
JOIN element e
ON e.id_tree = t.id
WHERE 1 = ( SELECT COUNT(*)
FROM element e2
WHERE e2.id_tree = e.id_tree
)
UNION ALL
SELECT e.tag, 3 AS flag, t.rgt AS pt, e.`desc`
FROM tree t
JOIN element e
ON e.id_tree = t.id
WHERE 1 < ( SELECT COUNT(*)
FROM element e2
WHERE e2.id_tree = e.id_tree
)
) r
ORDER BY r.pt, r.tag;
+---------------------+
| xml_teil |
+---------------------+
| <A>content A |
| <B>content B |
| </B> |
| <D>content D |
| <E>content E |
| </E> |
| <F>content F |
| <G>content G</G> |
| <G2>content G1</G2> |
| <G3>content G2</G3> |
| </F> |
| </D> |
| <C>content C</C> |
| <C1>content C1</C1> |
| <H>content H |
| </H> |
| </A> |
+---------------------+
17 rows in set (0.00 sec)
mysql>