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

Michael Noze
Michael Noze used Ask the Experts™
on
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.

IE:
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!
Comment
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

Commented:
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;
GO

/*RESULTS
----------
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...

Author

Commented:
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