need help with a statement - 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

General SQL Server Forums

New to SQL Server Programming

need help with a statement

Author

Topic

jmoore_2020
Starting Member

1 Post

Posted - 2010-07-30 : 13:06:18

anyone know how to display which sql input parameters are not in the database? for example select * from table where ID='number,number2,number3,etc'. number 1 and 2 are entry in the database. number 3 is not in the database. i need a statement that will display which numbers have a result and which do not. number 1 has a entry number 2 has an entry number 3 is empty number 4 is empty. etc

thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-30 : 13:41:20

Please be more specific with sample table structure, sample data and sample output.

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-30 : 14:00:59

quote:
Originally posted by jmoore_2020

anyone know how to display which sql input parameters are not in the database? for example select * from table where ID='number,number2,number3,etc'. number 1 and 2 are entry in the database. number 3 is not in the database. i need a statement that will display which numbers have a result and which do not. number 1 has a entry number 2 has an entry number 3 is empty number 4 is empty. etc

thanks

If I understand this correctly...you already have a list of numbers that you want to check right?

Create a temp table and insert them

CREATE TABLE #T (number VARCHAR(100))
INSERT #T
SELECT 'number1'
UNION ALL SELECT 'number2'
UNION ALL SELECT 'number3'
UNION ALL SELECT 'number4'

Join this table to your main table to find the missing values.

SELECT a.number
FROM #T a
LEFT JOIN table b on a.number = b.ID
WHERE b.ID IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources