Iterate through records returned by a Query.

Question

I have a query which returns few records. The Query result has a column 'ACCTID'. Now  for each record i need to take the ACCTID and fetch record from other table. I tried it to do in C# but using loop, but it is taking lot of time. Pls suggest me how to do the same in SQL server side.

 Overview of query is like below:

 

Main query: 

Select NAME, CANCEL_DATE..., ACCTID from Table1; 

 

What i need to do for ACCTID now is fetch data from two other tables for ACCTID fetched from above main query

 :

select * from table2 where ACCTID=@ACCTID 

select * from table3 where ACCTID=@ACCTID

 

 

In the end i need to return all the records fetched from above two queries.