hi, im looking for a better way to create subquery in my query..
select par_third_party.prodID,par_label.label,par_label.description,
par_label.tablename,sname=(case par_label.columnname
when 'product_name' then (select par_products.product_name from par_products where par_products.id='608')
when 'launch_date' then (case par_view_prodcategory.frmtype when 'One-Time Submission' then (select convert(nvarchar,par_products.launch_date_one,108) from par_products where par_products.id='608') else (select convert(nvarchar,par_products.launch_date,108) from par_products where par_products.id='608') end)
when 'objectives' then (select cast(par_products.objectives as nvarchar) from par_products where par_products.id='608')
end),
par_label.columnname,par_view_prodcategory.frmtype,
par_label.htmltype,par_label.page,par_third_party.remarks,par_label.tablename,par_label.columnname
from par_products
inner join par_third_party on par_products.id=par_third_party.prodid
inner join par_label_consultation on dbo.par_label_consultation.prodid=par_products.id
inner join par_label on par_label_consultation.labelid=par_label.id
inner join par_view_prodcategory on par_view_prodcategory.productid=par_third_party.prodID
where par_third_party.status_replied is null and par_products.id='608'

my problem is, par_label.columnname has many variables inside it..
i was wondering if i can select columnname from tablename at the same time..
eg: select columnname from tablename where id='292'
sname='Prod1'