sql server - SQL join Case Results with Data from Select -

what i'm trying accomplish have results of case include results of data. cases 1 , 2 go table resulting in 2 columns id | status | statusdescription. i'd join results of data along results of case.

i have table results , 2 tables being joined in case.

@zipcode    varchar(5), @tribal     varchar(5)   declare @status varchar(30) select @status = case when exists (select zipcodecoverage.zipcode, zipcodecoverage.state, serviceplan, plan_name  [lifelinereference].[dbo].[zipcodecoverage]     join [vcaredata].[dbo].[tbl_plan] on [zipcodecoverage].[state]= [plan_state]  zipcode = @zipcode , is_tribal = @tribal , life_line = 'y')   '1'  else 2  end select * [lifelinereference].[dbo].[lifelineplanstatuscodes]     id = @status 

status code table

id |   status     | statusdescription 1  |   success    | transaction may proceed 2  |   fail       | not continue 

what need results be

id |  status | statusdescription | zipcode | state |  serviceplan | plan_name 

i think got it. here came with

@zipcode    varchar(5), @tribal     varchar(5)   declare @status varchar(30)   select @status = case when exists (select zipcodecoverage.zipcode, zipcodecoverage.state, serviceplan, plan_name  [lifelinereference].[dbo].[zipcodecoverage]     join [vcaredata].[dbo].[tbl_plan] on [zipcodecoverage].[state]=[plan_state]        zipcode = @zipcode , is_tribal = @tribal , life_line = 'y')  '1'   else 2  end    select *  ,(select zipcodecoverage.zipcode    [lifelinereference].[dbo].[zipcodecoverage]     join [vcaredata].[dbo].[tbl_plan] on [zipcodecoverage].[state]=[plan_state]        zipcode = @zipcode , is_tribal = @tribal , life_line = 'y') zipcode  ,(select zipcodecoverage.state   [lifelinereference].[dbo].[zipcodecoverage]     join [vcaredata].[dbo].[tbl_plan] on [zipcodecoverage].[state]=[plan_state]        zipcode = @zipcode , is_tribal = @tribal , life_line = 'y') state     ,(select serviceplan   [lifelinereference].[dbo].[zipcodecoverage]     join [vcaredata].[dbo].[tbl_plan] on [zipcodecoverage].[state]=[plan_state]        zipcode = @zipcode , is_tribal = @tribal , life_line = 'y') serviceplan   ,(select plan_name  [lifelinereference].[dbo].[zipcodecoverage]     join [vcaredata].[dbo].[tbl_plan] on [zipcodecoverage].[state]=[plan_state]        zipcode = @zipcode , is_tribal = @tribal , life_line = 'y') plan_name [lifelinereference].[dbo].[lifelineplanstatuscodes]     id = @status 

giving me results of:

 id status  statusdescription   zipcode state   serviceplan plan_name   1 success service available    48322   mi  abc michigan wireless xxxx service free250