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
 General SQL Server Forums
 New to SQL Server Programming
 Problem ( SELECT statement)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/01/2013 :  05:52:23  Show Profile  Reply with Quote
CREATE TABLE testTab ( id int, name varchar(10))
insert into testTab values(1, 'chandu'), (2, 'abcd'),(4, 'xxx')

I will pass set of values as comma separated (Eg: 2,3,5)
Then it has to display the
ids
3
5

That means the ids which are not existed in the table

--
Chandu

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 02/01/2013 :  06:32:14  Show Profile  Visit webfred's Homepage  Reply with Quote
--CREATE TABLE testTab ( id int, name varchar(10))
--insert into testTab values(1, 'chandu'), (2, 'abcd'),(4, 'xxx')

select dt.number as id
from
(select number from master..spt_values where type = 'p' and number in (2,3,5))dt
left join testTab t on t.id = dt.number
where t.id is null




Too old to Rock'n'Roll too young to die.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/01/2013 :  06:38:55  Show Profile  Reply with Quote
hi webfred,
Those ids are not real... They may 2428492,437939, 23242 like this

--
Chandu
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 02/01/2013 :  06:40:34  Show Profile  Visit webfred's Homepage  Reply with Quote
Where do they come from? Is there a chance to have them in a table? How much numbers we are talking about?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/01/2013 :  06:42:39  Show Profile  Reply with Quote
so many values. they are getting ids from front-end.. they don't want to declare a temp table also


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/01/2013 :  06:44:46  Show Profile  Reply with Quote


CREATE TABLE testTab ( id int, name varchar(10))
insert into testTab values(1, 'chandu'), (2, 'abcd'),(4, 'xxx'),(437939,'gfergerg')

DECLARE @list varchar(100)
SET @list='2,3,5,2428492,437939, 23242'

SELECT *
FROM(
SELECT m.n.value('.','int') AS id
FROM (SELECT CAST('<Node><Row>' + REPLACE(@list,',','</Row><Row>') + '</Row></Node>' AS xml ) AS x) t
CROSS APPLY x.nodes('/Node/Row')m(n)
)m
WHERE NOT EXISTS(SELECt 1 FROM testTab WHERE id=m.id)

output
-------------------------
id
-----------
3
5
2428492
23242




EDIT: Added your latest data as well. it will work for any id values
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Edited by - visakh16 on 02/01/2013 06:47:09
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/01/2013 :  06:54:16  Show Profile  Reply with Quote
Thank you visakh...


--
Chandu
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 02/01/2013 :  06:54:41  Show Profile  Visit webfred's Homepage  Reply with Quote
declare @array varchar(1000)
set @array = '2,3,5'

select col1 as id
from
(select * from dbo.fnParseArray(@array,','))dt
left join testTab t on t.id = dt.col1
where t.id is null

-- the function is like this
CREATE FUNCTION [dbo].[fnParseArray] (@array VARCHAR(max),@separator CHAR(1))
RETURNS @T Table (occ int,col1 varchar(50))
AS
BEGIN
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(max)
declare @occurence int =1
if (left(@array,1)=@separator)
begin
set @array=stuff(@array,1,1,'')
end
if (right(@array,1)<>@separator)
begin
SET @array = @array + @separator
end
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
INSERT into @T VALUES (@occurence,@array_value)
set @occurence = @occurence + 1
SELECT @array = STUFF(@array, 1, @separator_position, '')
END
RETURN
END


Too old to Rock'n'Roll too young to die.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 02/01/2013 :  06:55:13  Show Profile  Visit webfred's Homepage  Reply with Quote



Too old to Rock'n'Roll too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/01/2013 :  07:04:07  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Thank you visakh...


--
Chandu


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.08 seconds. Powered By: Snitz Forums 2000