SQL: How do I refer to the result of a previous query?

Question

Suppose I have a SQL query that looks like this:

SELECT fName from employees where ssn=123456789;

Suppose I want to follow the previous query with another one:

SELECT fName from records WHERE ssn=123456789;
SELECT lName from records WHERE fName=(the result of the previous query)

What do I put in for (the result of the previous query) to make this return the last names from records where the fName matches the presumably unique record where ssn=123456789?

I know that this is an unrealistic example, but what I'm asking is, "How do I refer to the result of my previous query?"

BTW, if it makes any difference, I'm using MS SQL Server 2008. Thanks!

Answer 1

There are three obvious ways of doing this. You can use SQL variables, temp tables (or table variables, they are roughly equivalent) or you can use a subquery.

SQL Variables

Here you store your desired value in a variable (the @fname piece of code below) and then you can use this later. With this approach you want to be careful that your SSN really is the unique identifer.

Declare @fname Varchar(50)

Select @fname = fname 
from employees 
Where ssn = 123456789

Select lName from records
where fname = @fname

Temp Table

With a temp table you create a table of the records that you want. This can then be used later on, and to get the records you want you then use the temp table within a sub query.

You can either declare the temp table as a separate create table statement, or declare it as part of your select as I do below.

Select fname into #temptable
From employees
Where ssn = 123456789

Select lName from records
where fname in 
(
    Select fname 
    from #temptable
)

Sub Query

This is actually quite similar to the temp table approach, but you inline the temp table part, so you can't refer to it again later.

Select @fname = fname 
from employees 
Where ssn = 123456789

Select lName from records
where fname in 
(
   Select fname 
   from employees 
   Where ssn = 123456789 
)