In that case, you'll want to go with a multi-statement table valued function. Check in BOL for details, but here's a quick example.

CREATE FUNCTION dbo.udf_functest(@ID int)

RETURNS @ReturnTable TABLE (TranID int, Amount money)

AS

BEGIN

DECLARE @tempTable TABLE (ID int, TranID int, Amount money)

INSERT INTO @tempTable

SELECT ID,

TransactionID,

Amount

FROM dbo.Table1

WHERE ID = @ID

INSERT INTO @ReturnTable

SELECT TranID,

Amount

FROM dbo.Table2 t1

INNER JOIN @tempTable t2 ON t1.ID = t2.ID

RETURN

END

GO