Extract data when a column's value is in another column

Michael Noze
Michael Noze used Ask the Experts™
Hi Experts,

I have a table with 2 columns (Last name) and (First name).

I'd like to extract all the first name that has the same value as another column value.

The db is:
First name  |  Last name
Olivia                  Jones
Jones                  Mertil
Nicholas             Jay
Avril                    Lavigne
Charles              Brown
Annie                 Charles

The result would be the table

First Name | Last Name
Jones               Mertil
Charles           Brown

Thank you!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You may use IN operator in WHERE clause:
DECLARE @t TABLE (FirstName varchar(20), LastName varchar(20))
INSERT INTO @t VALUES ('Olivia', 'Jones'), ('Jones', 'Mertil'), ('Nicholas', 'Jay'), ('Avril', 'Lavigne'), ('Charles', 'Brown'), ('Annie', 'Charles')

SELECT FirstName, LastName 
  FROM @t
 WHERE FirstName IN (SELECT LastName FROM @t)

Open in new window

Nakul VachhrajaniTechnical Architect, Capgemini India

Depending upon the size of the table, you may also want to evaluate using JOINs:
DECLARE @userNames TABLE (FirstName varchar(20), LastName varchar(20))
INSERT INTO @userNames (FirstName, LastName)
VALUES ('Olivia'  , 'Jones'  ), 
       ('Jones'   , 'Mertil' ), 
       ('Nicholas', 'Jay'    ), 
       ('Avril'   , 'Lavigne'), 
       ('Charles' , 'Brown'  ), 
       ('Annie'   , 'Charles');

SELECT un.FirstName, un.LastName
FROM @userNames AS un
INNER JOIN @userNames AS iun ON un.FirstName = iun.LastName;

FirstName            LastName
-------------------- --------------------
Jones                Mertil
Charles              Brown

Open in new window

(NOTE: Either method - using IN or JOIN is okay - subject to your data conditions).
Just remember, even when JOIN works well in this simple case it will provide duplicate results on output in many other cases...


Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial