Menu
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.
233
If we need to query a table based on some set of values for a given column, we can simply use the IN clause. But if query need to be performed based on multiple columns, we could not use IN clause(grepped in SO threads.) From other SO threads, we can circumvent this problem using joins or exists clause etc.
To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.
When we have to select multiple columns along with some condition, we put a WHERE clause and write our condition inside that clause. It is not mandatory to choose the WHERE clause there can be multiple options to put conditions depending on the query asked but most conditions are satisfied with the WHERE clause.
You can use the OR condition in the WHERE clause to test multiple conditions where the record is returned if any one of the conditions are met. This example uses the WHERE clause to define multiple conditions, but instead of using the AND condition, it uses the OR condition.
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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
