asked on

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

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!

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nakul Vachhrajani

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).

Pavel Celba

Just remember, even when JOIN works well in this simple case it will provide duplicate results on output in many other cases...

Michael Noze

ASKER

Thank you!