CODEWITHSUNDEEP
sql
Talon Ellithorpe
Talon Ellithorpe

Reputation: 21

SQL - select results from multiple columns WHERE row 1 and row 2 are true

I'm trying to get the birthdate for people from a table where firstname and lastname are in the same row. I'm dealing with 100's of names and I cannot get unique results.

I've tried IN firstname AND lastname, however I am getting matches that are not in the same row.

example: Table "test"

FirstName LastName
John Doe
John Adams
Jane Doe
Molly Jetson
Mary Wayne 

SELECT firstname,lastname
FROM test
WHERE lastname IN ( 
'Doe',
'Adams',
'Jetson'
)
AND
firstname IN (
'John',
'John',
'Molly'
);

I only want cases where the firstname matches the lastname in the second list, however I'm getting ANY result where the firstname is found OR the lastname is found, so I get both "John Doe" AND "Jane Doe" in the result because the firsname was found AND the lastname was also found.

What I want to return would be:

John Doe John Adams Molly Jetson

NOT: John Doe Jane Doe John Adams Molly Jetson

Upvotes: 2

Views: 908

Answers (3)

Afroz
Afroz

Reputation: 1092

You can use Table Value Constructor from SQL Server 2008 onwards

SELECT t1.FirstName , t1.LastName
FROM Test t1 
JOIN (
    VALUES ('John','Doe'),
           ('John','Adams'),
           ('Molly','Jetson')
)t2(FirstName, LastName)
    ON t1.FirstName = t2.FirstName and t1.LastName = t2.LastName

Upvotes: 1

CactusCake
CactusCake

Reputation: 990

I would recommend concatenating for a tidier where clause:

SELECT
firstname,
lastname

FROM
test

WHERE
firstname + ' ' + lastname IN ('John Doe', 'John Adams', 'Molly Jetson')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You need to expand the logic to use and and or:

SELECT firstname, lastname
FROM test
WHERE (lastname = 'Doe' and firstname = 'John') OR
      (lastname = 'Adams' and firstname = 'John') OR
      (lastname = 'Jetson' and firstname = 'Molly')

Upvotes: 0

Related Questions