1

I want to learn how to create tree table (it's quite easy) and select records from it. The problem is that any of example codes it not working for me... All recursive examples begin with "WITH" word which "is not valid at this position" as MySQL Workbench is saying. Example: http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL

I searched for example codes for hours but I didn't find any working code. That's obvious it's something wrong on my side but I have no idea what :( What should I change in order to use those examples? Maybe I don't know about something which is not written in examples? I'm pretty new to MySQL so that maybe some really stupid thing and I'm wasting much time on it :)

Let's say I created tree table like this and put some data:

CREATE TABLE t
(
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `parent` bigint,
    `depth` int,
    PRIMARY KEY (`id`)
);

INSERT INTO t (parent,depth) VALUES (null,1),(1,2),(1,2),(1,2),(3,3),(4,3),(3,3),(7,4);

How should look the query to get all children (all depths) for main parent (the first record)?

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
ElChupacabra
  • 1,045
  • 10
  • 18
  • MySQL does not support CTEs (Common Table Extensions) and do not provide native tree structure support. See [this post](http://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree) for more information and some suggestions. [This answer](http://stackoverflow.com/a/28366310/2055998) could also be of interest. – PM 77-1 Feb 27 '15 at 22:34
  • Thanks alot. The most helpful is the link that someone posted on one of posts you wrote about: http://www.slideshare.net/billkarwin/models-for-hierarchical-data It's explaining tree data in very newbie-friendly language. – ElChupacabra Feb 28 '15 at 17:35

0 Answers0