SQL Tree structure parent child

Question

I have the following data:

                id                  parent_id           
                ------------------  ------------------  
                Editor              null
                Printer             Editor
                TextWritingProggie  Printer
                LaTeX               TextWritingProggie
                OOfficeWriter       TextWritingProggie
                PhoneBook           TextWritingProggie

I am querying for the PhoneBook id and all its parents. It should be a hierarchy that leads to the Editor as his parent is null and he is connected via the Printer which is connected via the TextWritingProggie which then is connected to the Phonebook.

I imagine that using SQL is more preferred however it might need to be handled via PHP.

Here's what Ive got so far but it stops after records that arent containing TextWritingProggie

SELECT * FROM acl_resources 
WHERE id = 'TextWritingProggie' OR parent_id = 'TextWritingProggie' 
ORDER BY COALESCE(parent_id, id), parent_id IS NOT NULL, id

Any help would be great

possible duplicate of [mysql recursive(tree) parent child category](http://stackoverflow.com/questions/18083546/mysql-recursivetree-parent-child-category) — Alex, Feb 25 '15 at 03:00

score 0 · Answer 1 · answered Feb 25 '15 at 03:10

Some RDBMS products offer built in hierarchical querying. For example, Oracle SQL has a START WITH ... CONNECT BY syntax, in which you could use this sort of query to get your result.

 SELECT id, SYS_CONNECT_BY_PATH(parent_id, '/'), LEVEL -- Oracle
   FROM res
  WHERE id='PhoneBook'
CONNECT BY prior id=parent_id
  ORDER BY LEVEL DESC

But not MySQL. You have to fake it somehow. One way is to retrieve the data and build the hierarchy in your application's memory.

Another way is with a sequence of LEFT JOIN operations that's longer than the expected depth of the hierarchy. This query gets a bit ugly, but it works. Each row of such a query contains the "genealogy" of the row. Here's an example for your data (http://sqlfiddle.com/#!2/bab1d/4/0):

select a.id a, b.id b, c.id c, d.id d, e.id e, f.id f, g.id g  /*MySQL*/
  from res a
  left join res b on a.parent_id = b.id
  left join res c on b.parent_id = c.id
  left join res d on c.parent_id = d.id
  left join res e on d.parent_id = e.id
  left join res f on e.parent_id = f.id
  left join res g on f.parent_id = g.id
where a.id = 'PhoneBook'

The result of this particular query is

|         A |                  B |       C |      D |      E |      F |      G |
|-----------|--------------------|---------|--------|--------|--------|--------|
| PhoneBook | TextWritingProggie | Printer | Editor | (null) | (null) | (null) |

The logic that's coded into CONNECT BY PRIOR id = parent_id in an Oracle hierarchical query shows up here as the repeated ON clauses for the repeated LEFT JOIN operations.

Yet another way is faking the recursive join with a stored procedure, described in [this blog post](http://guilhembichot.blogspot.jp/2013/11/with-recursive-and-mysql.html). — Amadan, Feb 25 '15 at 03:13
So I need to create a query with the exact number of parents? what if it goes beyond g? — somejkuser, Mar 04 '15 at 21:55
As I wrote, to use this cheesy technique with the sequence of `left join` operations, the sequence needs to be as long or longer than the expected depth of the hierarchy. If the hierarchy is too deep, it won't show the whole inheritance list of some items. — O. Jones, Mar 06 '15 at 21:54

SQL Tree structure parent child

1 Answers1