CODEWITHSUNDEEP
sql-serversql-server-2008selectin-clause
opensas
opensas

Reputation: 63395

SQL Server - IN clause with multiple fields

Is it possible to include in a IN clause multiple fields? Something like the following:

select * from user
where code, userType in ( select code, userType from userType )

I'm using ms sql server 2008

I know this can be achieved with joins and exists, I just wanted to know if it could just be done with the IN clause.

Upvotes: 20

Views: 65872

Answers (8)

Caius Jard
Caius Jard

Reputation: 74595

Only with something horrific, like

select * from user
where (code + userType) in ( select code + userType from userType )

Then you have to manage nulls and concatenating numbers rather than adding them, and casting, and a code of 12 and a usertype of 3 vs a code of 1 and a usertype of 23, and...

..which means you start heading into perhaps something like:

--if your SQLS supports CONCAT
select * from user
where CONCAT(code, CHAR(9), userType) in ( select CONCAT(code, CHAR(9), userType) from ... )

--if no concat
select * from user
where COALESCE(code, 'no code') + CHAR(9) + userType in ( 
  select COALESCE(code, 'no code') + CHAR(9) + userType from ... 
)

CONCAT will do a string concatenation of most things, and won't zip the whole output to NULL if one element is NULL. If you don't have CONCAT then you'll string concat using + but anything that might be null will need a COALESCE/ISNULL around it.. And in either case you'll need something like CHAR(9) (a tab) between the fields to prevent them mixing.. The thing between the fields should be southing that is not naturally present in the data..

Tis a shame SQLS doesn't support this, that Oracle does:

where (code, userType) in ( select code, userType from userType )

but it's probably not worth switching DB for; I'd use EXISTS or a JOIN to achieve a multi column filter

So there ya go: a solution that doesn't use joins or exists.. and a bunch of reasons why you shouldn't use it ;)

Upvotes: 5

Rick Savoy
Rick Savoy

Reputation: 341

I had to do something very similar but EXISTS didn't work in my situation. Here is what worked for me:

UPDATE tempFinalTbl
SET BillStatus = 'Non-Compliant'
WHERE ENTCustomerNo IN ( SELECT DISTINCT CustNmbr
             FROM tempDetailTbl dtl
            WHERE dtl.[Billing Status] = 'NEEDS FURTHER REVIEW'
              AND dtl.CustNmbr = ENTCustomerNo 
              AND dtl.[Service] = [Service]) 
  AND [Service] IN  ( SELECT DISTINCT [Service] 
             FROM tempDetailTbl dtl
            WHERE dtl.[Billing Status] = 'NEEDS FURTHER REVIEW'
              AND dtl.CustNmbr = ENTCustomerNo 
              AND dtl.[Service] = [Service])

EDIT: Now that I look, this is very close to @v1v3kn's answer

Upvotes: 0

SHD
SHD

Reputation: 1

select * from user
where (code, userType) in ( select code, userType from userType );

Upvotes: -3

Oded
Oded

Reputation: 498904

Not the way you have posted. You can only return a single field or type for IN to work.

From MSDN (IN):

test_expression [ NOT ] IN 
    ( subquery | expression [ ,...n ]
    ) 

subquery - Is a subquery that has a result set of one column. 
           This column must have the same data type as test_expression.

expression[ ,... n ] - Is a list of expressions to test for a match. 
                       All expressions must be of the same type as 
                       test_expression.

Instead of IN, you could use a JOIN using the two fields:

SELECT U.* 
FROM user U
  INNER JOIN userType UT
    ON U.code = UT.code
    AND U.userType = UT.userType

Upvotes: 16

pavanred
pavanred

Reputation: 13793

You can either use joins 

SELECT * FROM user U 
INNER JOIN userType UT on U.code = UT.code 
AND U.userType = UT.userType

Upvotes: 0

abc def foo bar
abc def foo bar

Reputation: 2388

I don't think that query is quite portable,it would be safer to use something like

select * from user
where code in ( select code from userType ) and userType in (select userType from userType)

Upvotes: -2

cdhowie
cdhowie

Reputation: 168958

You could use a form like this:

select * from user u
where exists (select 1 from userType ut
              where u.code = ut.code
                and u.userType = ut.userType)

Upvotes: 10

Sam Holloway
Sam Holloway

Reputation: 2009

How about this instead:

SELECT user.* FROM user JOIN userType on user.code = userType.code AND user.userType = userType.userType

Upvotes: 0

Related Questions