Q:

Using data from previous query

I want use a query like such
SELECT personId
FROM Person p 
Inner Join address a on p.personId=a.personId
WHERE(a.created > GETDATE() -10)

I want to then use this data to filter this query
SELECT * 
FROM accounts a
WHERE a.person /*is in the results of the previous query */

How can I do this?

A:

This should do the trick (assuming you have the column personId in your account table).
SELECT * 
FROM accounts a
WHERE a.personId in (
                SELECT DISTINCT personId
                FROM Person p 
                Inner Join address a on p.personId=a.personId
                WHERE(a.created > DATEADD(d,-10,GETDATE())))

Alternatively, rather than using a subquery you could just join the 3 tables directly:
SELECT
    C.*
FROM
    accounts C
INNER JOIN
    Person P ON P.personId = C.personId
INNER JOIN
    [Address] A ON A.personId = P.PersonId
WHERE
    A.created > DATEADD(d,-10,GETDATE())

A:

A nice feature in SQL Server 2008 is the Common Table Expression (CTE). It's basically an in-line view and makes the query that is actually returning the data much cleaner to read.
WITH people
AS
(
SELECT personId
FROM Person p 
Inner Join address a on p.personId=a.personId
WHERE(a.created > GETDATE() -10)
)

SELECT * 
FROM accounts a
INNER JOIN people p ON a.personId = p.personId

