-- Some reformatting to make the code easier to read

-- and a small change to make the logic a little easier to follow.

-- Please check that this query returns the same results as the original.

-- Please post some sample data. If you're not sure of how to do this,

-- there's an article linked in my sig. The sample data should contain at

-- least one set which has members in all 6 level;s of the hierarchy.

SELECT

L0.comp_id AS comp_id,

L1.comp_code AS parent_comp_code,

L1.name AS parent_comp_name,

L2.comp_id AS grand_parent_comp_id,

L2.comp_code AS grand_parent_comp_code,

L2.name AS grand_parent_comp_name,

L3.comp_id AS level0_comp_id,

L3.comp_code AS level0_comp_code,

L3.name AS level0_comp_name,

L4.comp_id AS level1_comp_id,

L4.comp_code AS level1_comp_code,

L4.name AS level1_comp_name,

L5.comp_id AS second_level_comp_id,

L5.comp_code AS second_level_comp_code,

L5.name AS second_level_comp_name

FROM dbo.comp L0

LEFT JOIN dbo.comp L1 ON L1.comp_id = L0.parent_comp_id

LEFT JOIN dbo.comp L2 ON L2.comp_id = L1.parent_comp_id

LEFT JOIN dbo.comp L3 ON L3.comp_id = L2.parent_comp_id

LEFT JOIN dbo.comp L4 ON L4.comp_id = L3.parent_comp_id

--LEFT JOIN dbo.comp L5 ON L5.comp_id = one less table reference

OUTER APPLY (

SELECT TOP 1

lvl2_comp.comp_id,

lvl2_comp.comp_code,

lvl2_comp.name

FROM dbo.comp lvl2_comp

INNER JOIN dbo.comp top_comp

ON lvl2_comp.parent_comp_id = top_comp.comp_id

AND top_comp.company_code = L0.company_code -- outer reference

INNER JOIN dbo.comp_chain bc

ON bc.comp_id = lvl2_comp.comp_id

AND bc.chain_comp_id = L0.comp_id -- outer reference

WHERE top_comp.parent_comp_id IS NULL OR top_comp.parent_comp_id = '0'

ORDER BY lvl2_comp.comp_id

) L5