join - MySQL: How to query for a column and include information from related columns in the same table? -

in mysql, have table this:

+-----------------------+ |         assets        | +-----------------------+ | id |   name  | rootid | +----+---------+--------+ |  1 | asset |    1   | +----+---------+--------+ |  2 | asset b |    2   | +----+---------+--------+ |  3 | asset c |    3   | +----+---------+--------+ |  4 | asset d |    2   | +----+---------+--------+ |  5 | asset e |    3   | +----+---------+--------+ |  6 | asset f |    3   | +----+---------+--------+ 

not greatest table structure, know...but i'm stuck now.

i trying write single query that, given id value, return rootid , rootname if there 2 (2) rows same rootid. otherwise columns should null.

so, using table above, if given id of 4 query should return:

+----------------------------------+ |              assets              | +----------------------------------+ | id |   name  | rootid | rootname | +----+---------+--------+----------+ |  4 | asset d |    2   |  assetb  | +----+---------+--------+----------+ 

but if given other id value, such 5, should return:

+----------------------------------+ |              assets              | +----------------------------------+ | id |   name  | rootid | rootname | +----+---------+--------+----------+ |  5 | asset e |  null  |   null   | +----+---------+--------+----------+ 

any on appreciated. think require subquery count , possibly group by, i'm not sure how articulate it...

thanks in advance!

the following should implement logic:

select id, name,        (case when cnt = 2 rootid end) rootid,        (case when cnt = 2 ari.name end) rootname assets join      (select rootid, count(*) cnt       assets       group rootid      ) ri      on a.rootid = ri.rootid left join      assets ari      on a.rootid = ari.id id = 4; 

you can as:

select a.id, a.name,        (case when a.cnt = 2 a.rootid end) rootid,        (case when a.cnt = 2 ari.name end) rootname (select a.*,              (select count(*) assets a2 a2.rootid = a.rootid) cnt       assets       id = 4      ) left join      assets ari      on a.rootid = ari.id; 

without full aggregation, perform better.

here sql fiddle illustrating them.