asp.net - T-SQL Select columns from one table where id in another table -

i asked question time back: adding attachments taks records in t_sql , got perfect answer question.

however need take 1 step further. if have following (based on previous question's answer)

select taskid, taskdescription, tasktype  tasks  tasktype = 1 [first-table] 

i want able select

select attachmentid, taskid, [filename], filelocation  taskattachments  [where taskid in first-table] ; 

and need able return both tables asp.net

i can write query this:

select attachmentid, taskid, [filename], filelocation  taskattachments  taskid exists in (select taskid, taskdescription, tasktype                          tasks                         tasktype = 1); 

my actual queries longer lots of joins , second query becomes long , seems convoluted , requires selecting same data twice.

is there better way using actual data returned first select?

(btw, please forgive syntax errors. quick illustration, actual code works fine!!)

you can use common table expression in situation:

;with cte as(select taskid, taskdescription, tasktype tasks) select attachmentid, taskid, [filename], filelocation  taskattachments taskid in(select taskid cte tasktype = 1) 

edit:

if exists(select * tempdb.sys.tables [name] = '#tasks') begin    drop table #tasks; end;  --save temp table select taskid, taskdescription, tasktype #tasks tasks  --select temp table select taskid, taskdescription, tasktype #tasks  --select second table using temp table select attachmentid, taskid, [filename], filelocation  taskattachments taskid in(select taskid #tasks tasktype = 1)