Problem ( SELECT 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

Problem ( SELECT statement)

Author

Topic

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-01 : 05:52:23

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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-01 : 06:32:14

--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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-01 : 06:38:55

hi webfred,
Those ids are not real... They may 2428492,437939, 23242 like this

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-01 : 06:40:34

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-01 : 06:42:39

so many values. they are getting ids from front-end.. they don't want to declare a temp table also

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-01 : 06:44:46

[code]

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

[/code]

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-01 : 06:54:16

Thank you visakh...

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-01 : 06:54:41

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.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-01 : 06:55:13