mysql - Selecting child records from same table -

i have "company" table, has relation table "companycategorylink", has relation the "category". have query can select companies based on company id. have field in category table identifies parent category, can have nested categories. had make change design though, can "merge" categories. can mark category "merged" setting flag in parent category. multiple categories show one. thing is, have select companies linked merged categories linked parent category, , @ same time have still work none merged categories. shouldn't break non merged categories. have no idea how go doing this. i'm required post code snippet of have, here is:

select company.name companyname, company.description, company.logo, company.companyid, company.telephone, company.fax, company.address, company.city, category.name, category.categoryid company join categorycompanylink on categorycompanylink.companyid = company.companyid join category on categorycompanylink.categoryid = category.categoryid join company on category.combined = 1  category.categoryid =19 

it's incomplete though, i'm stuck. how go doing this?

this category table:

and company table

any appriciated. have no idea how continue

this might or might not looking (please rewrite question) sql fiddle demo.

the final query:

select * (   select node_category.* category parent_category     join category node_category on node_category.lft between parent_category.lft + 1 , parent_category.rgt - 1     parent_category.id = 2         , node_category.merged   union     select * category id = 2) category   left join company_has_category on category.id = category_id   left join company              on company_id = company.id; 

with tables:

create table company (   id int primary key auto_increment,   name varchar(20) not null ); insert company values (default, 'company 1'), (default, 'company 2'), (default, 'company 3');  create table category (   id int primary key auto_increment,   name varchar(20) not null,   merged bool not null default false,   lft int not null,   rgt int not null ); insert category values   (1,  'electronics',          false, 1,20),  (2,  'televisions',          false,  2,9),  (3,  'tube',                 false, 3,4),  (4,  'lcd',                  true, 5,6),  (5,  'plasma',               true, 7,8),  (6,  'portable electronics', false, 10,19),  (7,  'mp3 players',          false, 11,14),  (8,  'flash',                false, 12,13),  (9,  'cd players',           false, 15,16),  (10, '2 way radios',         false, 17,18) ;  create table company_has_category (   company_id int,   category_id int,   constraint foreign key (company_id) references company (id),   constraint foreign key (category_id) references category (id) );  insert company_has_category values (1, 1), (2, 1), (1, 2), (3, 2); 

some queries understand how works:

-- data select company.*, company_has_category.*, category.* company_has_category   left join company on company.id = company_id   right join category on category_id = category.id;  # id, name,        company_id, category_id, id, name,                merged, lft, rgt 1,    'company 1', 1,          1,           1,  'electronics',       0,      1,   20 2,    'company 2', 2,          1,           1,  'electronics',       0,      1,   20 1,    'company 1', 1,          2,           2,  'televisions',       0,      2,   9 3,    'company 3', 3,          2,           2,  'televisions',       0,      2,   9 null, null,        null,       null,        3,  'tube',              0,      3,   4 null, null,        null,       null,        4,  'lcd',               1,      5,   6 null, null,        null,       null,        5,  'plasma',            1,      7,   8 null, null,        null,       null,        6,  'port. electronics', 0,      10,  19 null, null,        null,       null,        7,  'mp3 players',       0,      11,  14 null, null,        null,       null,        8,  'flash',             0,      12,  13 null, null,        null,       null,        9,  'cd players',        0,      15,  16 null, null,        null,       null,        10, '2 way radios',      0,      17,  18   -- children of category 2 (without parent) have merged flag set select * category parent_category   join category node_category on node_category.lft between parent_category.lft + 1 , parent_category.rgt - 1   parent_category.id = 2         , node_category.merged ;  -- children merged set plus parent -- (if parent has merged flag set, union can dropped) select node_category.* category parent_category   join category node_category on node_category.lft between parent_category.lft + 1 , parent_category.rgt - 1   parent_category.id = 2         , node_category.merged union   select * category id = 2;