Clever Geek Handbook

WHERE ... IN with multiple columns

I am trying to get some users who meet certain conditions:

SELECT * FROM users
WHERE lastname IN ('Pitt','Jolie','Costner') 
AND firstname IN ('Brad','Angelina','Kevin')

But in this way, Kevin Jolie and Brad Costner will be shown that is NOT what I want.

So, I tried:

SELECT * FROM users
WHERE (lastname,firstname) IN ('Pitt','Brad'),('Jolie','Angelina'),('Costner','Kevin')

What is causing the error:

An expression of non-boolean type specified in a context where a condition is expected, near ','.

Do you know a query that will give the expected result?

Attn: Two or more columns are not always varchar columns, they can be of any type.

+3


source to share


2 answers


I would use a query like this (but I am using SQL-Server 2005, it may be ahead of the curve now):



SELECT * FROM users
WHERE 
    (firstname = 'Angelina' AND lastname = 'Jolie') 
OR
    (firstname = 'Brad' AND lastname = 'Pitt') 
OR
    (firstname = 'Kevin' AND lastname = 'Costner')
+1


source


Try it.

SELECT * FROM users
WHERE lastname+firstname IN ('PittBrad','JolieAngelina','CostnerKevin')


If the columns are zero then

SELECT * FROM users
WHERE ISNULL(lastname,'')+ISNULL(firstname,'') IN ('PittBrad','JolieAngelina','CostnerKevin')
+2


source






More articles:


All Articles