SQLServerCentral.com / SQL Server 2012 / SQL Server 2012 - T-SQL / Check to see if multiple VALUES exsit in a table / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 12:13:02 GMT20RE: Check to see if multiple VALUES exsit in a tablehttp://www.sqlservercentral.com/Forums/Topic1520685-3077-1.aspxYup, Its a standard LEFT JOIN problem.give me all the records in the starting table (list of email addresses supplied) and the matching record in the right hand table (stored email addresses) if it existsMon, 09 Dec 2013 04:36:57 GMTaaron.reeseRE: Check to see if multiple VALUES exsit in a tablehttp://www.sqlservercentral.com/Forums/Topic1520685-3077-1.aspx[quote][b]Minnesota - Viking (12/6/2013)[/b][hr]Thank you sir. I should start using temp tables. I was able to achive what I wanted.[/quote]The "Temp tables" you're speaking of was actually a Table Variable and it was just being used as a source of test data. It actually had little to do with the problem.Fri, 06 Dec 2013 17:42:08 GMTJeff ModenRE: Check to see if multiple VALUES exsit in a tablehttp://www.sqlservercentral.com/Forums/Topic1520685-3077-1.aspxThank you sir. I should start using temp tables. I was able to achive what I wanted.Fri, 06 Dec 2013 13:32:31 GMTMinnesota - Viking RE: Check to see if multiple VALUES exsit in a tablehttp://www.sqlservercentral.com/Forums/Topic1520685-3077-1.aspxI don't have adventureworks installed so I'll have to wing it..If you get the email list into a table it becomes pretty easy..[code="sql"]DECLARE @Wk TABLE ( RecId INT IDENTITY(1,1) NOT NULL, EmailAddress VARCHAR(100) NOT NULL )INSERT @Wk (EmailAddress ) VALUES ( 'ken0@adventure-works.com' ), ( 'terri0@adventure-works.com' ), ( 'roberto0@adventure-works.com' ), ( 'gail0@adventure-works.com' ), ( 'jossef0@adventure-works.com' ), ( 'dylan0@adventure-works.com' ), ( 'diane1@adventure-works.com' ), ( 'gigi0@adventure-works.com' ), ( '25terri0@adventure-works.com' ), ( '25roberto0@adventure-works.com' ), ( '25rob0@adventure-works.com' )SELECT States = CASE WHEN ea.EmailAddressID IS NULL THEN 'Email Address Is NOT Present' ELSE 'Email Address Is Present' END EmailAddressID, w.EmailAddressFROM @Wk w LEFT OUTER JOIN [AdventureWorks2012].[Person].[EmailAddress] ea ON w.EmailAddress = ea.EmailAddressGO[/code]CEWIIFri, 06 Dec 2013 12:55:41 GMTElliott WhitlowCheck to see if multiple VALUES exsit in a tablehttp://www.sqlservercentral.com/Forums/Topic1520685-3077-1.aspxI know how to check for a sinle vlaue but how do I chekc to see if multiple values exist. I need to check for certain email addresses from a list that I have. Let us say I ahve 3 email addresses, I want to check for all of them in a table and for eevery email address that is present I want to print something like "You email address is XXX" and if one of those 3 is not found my results should look like "You email address is XXX"YYYYY not found"You email address is ZZZZ"Please suggest. I'm atatching some TSQL that I tried on [b][AdventureWorks2012].[Person].[EmailAddress][/b][code="sql"]/****** Select ALL if where an email address is present in the list ******/SELECT EmailAddressID,EmailAddress FROM [AdventureWorks2012].[Person].[EmailAddress] WHERE EmailAddress IN ('ken0@adventure-works.com', --1'terri0@adventure-works.com', --2'roberto0@adventure-works.com',--3'gail0@adventure-works.com', --4'jossef0@adventure-works.com',--5'dylan0@adventure-works.com',--6'diane1@adventure-works.com',--7'gigi0@adventure-works.com',--8-- The below emails do not exist. '25terri0@adventure-works.com',--9'25roberto0@adventure-works.com',--10'25rob0@adventure-works.com'--11)/*Results:EmailAddressID EmailAddress8 diane1@adventure-works.com7 dylan0@adventure-works.com5 gail0@adventure-works.com9 gigi0@adventure-works.com6 jossef0@adventure-works.com1 ken0@adventure-works.com3 roberto0@adventure-works.com2 terri0@adventure-works.com*/-- Test to see if a single email address is present IF EXISTS ( SELECT EmailAddress FROM [AdventureWorks2012].[Person].[EmailAddress] WHERE EmailAddress IN ('25rob0@adventure-works.com') ) BEGIN SELECT 'Email address is presnt' ENDELSE SELECT 'Email Address is NOT present'; /*Results:Email Address is NOT present*/-- Test if multiple values are present:IF EXISTS ( SELECT EmailAddress FROM [AdventureWorks2012].[Person].[EmailAddress] WHERE EmailAddress IN ( 'ken0@adventure-works.com', --1 'terri0@adventure-works.com', --2 'roberto0@adventure-works.com',--3 'gail0@adventure-works.com', --4 'jossef0@adventure-works.com',--5 'dylan0@adventure-works.com',--6 'diane1@adventure-works.com',--7 'gigi0@adventure-works.com',--8 -- The below emails do not exist. '25terri0@adventure-works.com',--9 '25roberto0@adventure-works.com',--10 '25rob0@adventure-works.com'--11 ) ) BEGIN SELECT 'Email address is presnt' ENDELSE SELECT 'Email Address is NOT present'; /*Results:Email Address is present*/[/code]when I check multiples using EXISTS it works as per its design and says YES even if a single item is present. Please advise.Fri, 06 Dec 2013 11:04:32 GMTMinnesota - Viking