Use same value in 2 alias sql server -

i have query this.

select   ( select statusname    dbo.lms_mst_status    mainstatusid = a.parentid ) +'-'+ statusname value,    ( select statusname    dbo.lms_mst_status    mainstatusid = a.parentid ) +'-'+ statusname [text] dbo.lms_mst_status parentid > 0   , mainstatusid not null order [text] 

i have 2 alias i.e value , text values of both same. these columns handled in front end can't change front end code. can't change alias name. have made query above. have feeling affect performance of application since using sub-query both aliases. can optimize?

try left join:

select     b.statusname +'-'+ a.statusname [value],     b.statusname +'-'+ a.statusname [text], dbo.lms_mst_status left join dbo.lms_mst_status b on a.parentid = b.mainstatusid parentid > 0 , mainstatusid not null order [text] 

you can try inner join(suggested @damien_the_unbeliever):

select     b.statusname +'-'+ a.statusname [value],     b.statusname +'-'+ a.statusname [text], dbo.lms_mst_status inner join dbo.lms_mst_status b on a.parentid = b.mainstatusid parentid > 0 , mainstatusid not null order [text] 

which not create null values , fetch data if a.parentid = b.mainstatusid condition satisfied.