CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MNOPups
MNOPups

Reputation: 55

SQL Server Select additional record based on column value of first record

I am looking for a SQL query which will use a value in one record to select a value from an additional record in the same table, returning a value from each of the two records in a single row.

Suppose I have the following table where the ref_id column value will either be null or will contain the id value of another record. The id field is the primary key.

 id | name    | ref_id
----+---------+--------
 1  | Joe     | NULL
 2  | Sally   | NULL
 3  | Steve   | 2
 4  | James   | 1
 5  | George  | 2

I need a query that will return the following results:

 name    | ref_name    
---------+----------
 Joe     | NULL
 Sally   | NULL
 Steve   | Sally
 James   | Joe
 George  | Sally

Can someone suggest a solution?

Thank you in advance your your help.

Upvotes: 0

Views: 525

Answers (1)

M.Ali
M.Ali

Reputation: 69514

Test Data

DECLARE @TABLE TABLE( id INT, name VARCHAR(20),ref_id INT)
INSERT INTO @TABLE VALUES 
( 1,  'Joe',     NULL),
( 2,  'Sally',   NULL),
( 3,  'Steve',   2),
( 4,  'James',   1),
( 5,  'George',  2)

Query

SELECT A.Name , B.Name As Ref_Name
FROM @TABLE A LEFT JOIN @TABLE B
ON A.Ref_ID = B.id

Result

╔════════╦══════════╗
║  Name  ║ Ref_Name ║
╠════════╬══════════╣
║ Joe    ║ NULL     ║
║ Sally  ║ NULL     ║
║ Steve  ║ Sally    ║
║ James  ║ Joe      ║
║ George ║ Sally    ║
╚════════╩══════════╝

Upvotes: 1

Related Questions