Query for "is value in set"? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query for "is value in set"?

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2013-01-28 : 05:37:48

What is the correct syntax for this:

SELECT 1 IN(2,3,4) AS IsInSet

So plain and simply I want to know whether the value is there or not.

The actual use for this will be for determining whether a given project is an internal or external project to a given user. I have a table allocations like
USER PROJECT
1 2
2 1
3 2
1 1

from which I want to extract info like "User 1, Project 2 => Internal (because such a pair is in the table). User 3, Project 1 => External (because no such pair is in the table)."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 05:46:47

you can use left join for that


SELECT *,
CASE WHEN a.User IS NOT NULL THEN 'Internal' ELSE 'External' END
FROM YourTable  t
LEFT JOIN allocations a
ON a.USER = t.User
AND a.Project = t.Project

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2013-01-28 : 05:52:06

Ah, ok. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 06:13:05

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources