You can achieve the same thing with subqueries. If you're after better performance, I'm not sure that rewriting the query this way will improve things.

Also, consider that the semantics changed slightly, as now you're doing OUTER joins instead of INNER joins. Make sure this is ok in your case.

SELECT OS.NAME

,OS.ITEMID

,OS.PARENTID

,PARENTNAME = (

SELECT NAME

FROM OPERATIONALSTRUCTURE AS OS2

WHERE OS.PARENTID = OS2.ITEMID

)

,OS.HIERID

,HIERNAME = (

SELECT NAME

FROM OPERATIONALSTRUCTURE AS OS3

WHERE OS.HIERID = OS3.ITEMID

AND H.HIERID = OS3.HIERID

)

,OS.HIERLEVEL

,H.LEVELNAME

FROM OPERATIONALSTRUCTURE AS OS

INNER JOIN HIERLEVELS H

ON H.HIERLEVEL = OS.HIERLEVEL

ORDER BY OS.HIERID

,OS.PARENTID

,OS.NAME

Another possibility is CROSS APPLY:

SELECT OS.NAME

,OS.ITEMID

,OS.PARENTID

,PARENTNAME

,OS.HIERID

,HIERNAME

,OS.HIERLEVEL

,H.LEVELNAME

FROM OPERATIONALSTRUCTURE AS OS

INNER JOIN HIERLEVELS H

ON H.HIERLEVEL = OS.HIERLEVEL

CROSS APPLY (

SELECT NAME AS PARENTNAME

FROM OPERATIONALSTRUCTURE AS OS2

WHERE OS.PARENTID = OS2.ITEMID

) AS OS2

CROSS APPLY (

SELECT NAME AS HIERNAME

FROM OPERATIONALSTRUCTURE AS OS3

WHERE OS.HIERID = OS3.ITEMID

AND H.HIERID = OS3.HIERID

) AS OS3

ORDER BY OS.HIERID

,OS.PARENTID

,OS.NAME

Long story short, you always need to go back to the same table reading the information you need. I'm afraid there's no way around it.

Hope this helps

Gianluca