Nested Set model - Get child node with parent

dyip · #1 (**permalink**) 02-13-09, 17:45

Hi,

I have a database that is based on the nested set model. what i'm trying to do here is to run a query that gets the "leaf" nodes and it's parents. For example my table looks like this:

Code:

+-----------------------+
| name                         |
+-----------------------+
| ELECTRONICS              |
|   TELEVISIONS              |
|    TUBE                       |
|     LCD                         |
|     PLASMA                    |
|  PORTABLE ELECTRONICS |
|    MP3 PLAYERS            |
|      FLASH                    |
|    CD PLAYERS              |
|    2 WAY RADIOS          |
+-----------------------+

I'm trying to get this....

Code:

+----------------------+-------+
| name                       |  parent         |
+----------------------+-------+
| TUBE                         |     Television |
| LCD                           |     Television |
| PLASMA                     |     Television  |
| FLASH                         |     MP3 Players |
| CD PLAYERS                  |     PORTABLE ELECTRONICS|
| 2 WAY RADIOS           |     PORTABLE ELECTRONICS |
+----------------------+-------+

Any help would be appreciated. Thanks in advnace!

guelphdad · #2 (**permalink**) 02-13-09, 19:54

Have you read: this article which is where your table looks like it came from?

dyip · #3 (**permalink**) 02-13-09, 20:32

I did but the answer is not in there

rrozema · #4 (**permalink**) 11-11-10, 06:53

Any record's lft and rgt are always BETWEEN their parent's lft and rgt values. Your direct parent must be the one with the lft value closest to your's out of that set, i.e. the record with the highest lft value.

Internet Services

Web Development

Digital Marketing

Consumer Tech