Using data from previous query

Question

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?

Answer 1

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())