1

I am working with mysql and php and have a Parent Child table created in mysql. Every parent has 3 Childs. Every Child becomes also a Parent and has also 3 Childs and so on.

Now I need to write a function that gives me the last parent in the tree (depending on the selected parent ID) that does not have 3 Childs. So the next Child subscription can be dedicated to that parent.

Can this be done with mysql query or function?

Thanks, M

I use the following query:

SELECT t1.uid, t2.pid FROM cms_users AS t1 LEFT JOIN cms_users AS t2 ON t1.uid = t2.pid

Which gives mr the following output: 

t1.uid t2.pid
1      1 
1      1 
1      1 
2      2 
2      2 
2      2 
3      3 
4      NULL 
5      NULL

What I actualy need is a result like: 

p1.uid p2.pid 
1      3 
2      3 
3      1 
4      0 
5      0

This result also starts from the root Parent 1 I need to get the results starting from a selected uid somewhere in the three. Every parent has his own tree starting from his uid. Probably I need to write a stored procedure or something but this is all new to me and I don't know how to do this.

This is an example of how the tree looks like. http://www.musafreaks.com/images/tree.jpg User ID 1 has his own tree, even user 2, 3 and 4 have there own tree and so on.

casperOne
  • 73,706
  • 19
  • 184
  • 253
mario
  • 177
  • 2
  • 11
  • What does "depending on the selected parent ID" mean? Could you please post a sample recordset and a desired output of the query? – Quassnoi Dec 24 '10 at 17:03
  • Hope this one help:: http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries – Sashi Kant Feb 02 '13 at 09:23

1 Answers1

2

This problem is not trivial to solve within MySQL. Multiple strategies to manage hierarchical data are described in the manual. The rest of my answer is based on this article.

Your setup is sort of a "Adjacency List Model", so you could adapt the LEFT JOIN solution for your dataset.

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

This assumes that the parent field of the root node is null. Your possible new parent will be selected as t1. Note that this query will return all nodes which have no children, if you want to "fill" up each node with three children you'll have to extend the query a bit.

svens
  • 11,438
  • 6
  • 36
  • 55
  • Hello, thanks for your feedback. Unfortunelly I was not 100% correct in my explanation. What I want is the next parent that has no 3 childs. If he has less then 3 childs he should be available in the list. The search need also to be started at a certain selected parent ID. For example if I select a parent 10 levels deep in the tree it should give me the next parent (in the seleted parents tree. Every parrent has his own tree) that does not have 3 childs assigned. – mario Dec 24 '10 at 15:02