mysql - Join parent to child node in hierarchical table structure -

i have following sql grabbing tree node , it's parent:

select          c.id     ,   c.tag     ,   (             select                      s.id             treetable s              s.lft < c.lft , s.rgt > c.rgt             order s.rgt - c.rgt asc             limit 1         ) parent treetable c; 

the problem though want s.tag inside table, can't select 2 columns wtihin subquery. how can go refactoring sql able select 2 columns?

i've looked @ lot of resources on possibly trying left join, can't work. cant think of simple grouping operation either due needing use order , limit within subquery

edit: structure of table looks follows:

field     type              collation          null    key     default            privileges                       comment   --------  ----------------  -----------------  ------  ------  -------  --------------  -------------------------------  --------- id        int(10)           (null)             no      pri     (null)   auto_increment  select,insert,update,references                     tag       varchar(255)      latin1_swedish_ci  no              (null)                   select,insert,update,references            lft       int(11)           (null)             no      mul     (null)                   select,insert,update,references            rgt       int(11)           (null)             no      mul     (null)                   select,insert,update,references            

try this, using 2 inefficient sub selects

select  c.id, c.tag, (     select          s.id     treetable s      s.lft < c.lft , s.rgt > c.rgt     order s.rgt - c.rgt asc     limit 1 ) parent,  (     select          s.tag     treetable s      s.lft < c.lft , s.rgt > c.rgt     order s.rgt - c.rgt asc     limit 1 ) parent_tag treetable c; 

it seems logically correct. can't test sorry. in these case, if use often, should re-factor , design table better defined parent-child links