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

Question

Suppose I have an SQL query that looks like this:

SELECT fName from employees where ssn=123456789;

Suppose I want to execute a previous request with another:

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

What can I do (the result of the previous query)to return this name from the records where fName matches the supposedly unique record, where ssn = 123456789?

I know this is an unrealistic example, but I ask: "How can I refer to the result of my previous query?"

By the way, if that matters, I'm using MS SQL Server 2008. Thanks!

Answer 1

You can save the result of the first query in a variable and use this variable in the second query.

DECLARE @firstName VARCHAR(255)
SELECT @firstName = fName from employees where ssn=123456789

SELECT lName from records WHERE fName=@firstName

Answer 2

SELECT lName from records WHERE fName =(SELECT fName from employees where ssn=123456789)

 SELECT lName from records r
 INNER JOIN employees e ON (e.fName = r.fName)
 WHERE e.ssn = 123456789

CTE.

Answer 3

. SQL-, ( , ), .

SQL

( @fname ), . , SSN .

Declare @fname Varchar(50)

Select @fname = fname 
from employees 
Where ssn = 123456789

Select lName from records
where fname = @fname

Temp

temp . , , , -.

create table , .

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 very similar to the temp table approach, but you are embedding part of the temp table, so you cannot reference it later.

Select @fname = fname 
from employees 
Where ssn = 123456789

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