SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query for "is value in set"?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts
Posted - 01/28/2013 :  05:37:48  Show Profile  Reply with Quote
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

India
47173 Posts
Posted - 01/28/2013 :  05:46:47  Show Profile  Reply with Quote
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/
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts
Posted - 01/28/2013 :  05:52:06  Show Profile  Reply with Quote
Ah, ok. Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 01/28/2013 :  06:13:05  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000