asked on

How do I find path when leaf node is unknown?

I've used nested sets methodology proposed by Celko to create a tree for calculating MLM commissions. I need to find if any node meets certain characteristics (i.e. "rank") between the parent node and the terminating leaf node, when I do not know the left or right values of the leaf node or how many nodes are between the parent and the leaf.

Key fields are:
lft
rgt
idNumber
rank

Any suggestions? I am wrting in ASP using mySQL as database.

I could alternatively work from down up (in which I would know the leaf node) but need to find the first instance above the leaf node that meets the rank qualifications.

Thanks in advance for your help.

Roger Baklund

Wouldn't this find such nodes:

select * from tree 
where 
  lft between @parent_lft and @parent_rgt and
  rgt between @parent_lft and @parent_rgt and
  rank > @required_rank

Open in new window

ASKER CERTIFIED SOLUTION

joiko

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Mark Wills

Yep, that looks good. It is about the only way to achieve it, otherwise you end up with every branch, not just the path that contains your node.

Though doubt if we could have got to exactly the same solution, you seem to have a few extra bits n pieces n names...

Mark Wills

For what it is worth, my recommendation would be Delete with Refund. Unless the asker would prefer to accept their own posting as the answer and award points to cxr as the inspiration to their solution.

joiko

ASKER

Ok -- this is strange -- apparently, I don't know what I'm supposed to do here. I read the link -- I cancelled my account (free trial) first because I didn't get useful responses or much activity. Saw that there were new postings so I figured maybe I'm getting activity and maybe some ideas... only to find that I somehow did something wrong -- I don't see a choice to not to accept answer or to close out... what am I missing here? could someone explain or point me in the right direction.

Mark Wills

No, you have not done anything wrong.

This is part of a cleanup process to make sure EE is kept in a clean state.

You do not have to do anything really, the clean up process does catch and clean abandoned threads.

What is "good" form and the preferred interaction is for you to close the question as you see fit rather than add to the burdons of abandoned questions.

Given your comment, would suggest that you 'delete' the question.

joiko

ASKER

Thanks -- that's helpful!!