SQL Question - How do I get to see items from a list not in a table?

Hi Experts

If I have a list like 'y','i','for','xyz','abc', and I also have a table with one column containing items,
mytable.column1
y
xyz
i

How can I get in one sql what items on the list are missing in that table?

In this example I want to see 'for' and 'abc'
LVL 4
bfuchsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
If you are using SSMS I do not follow why you are unable to use a local variable
(they exist in MSSQL 2005 too)

but. if you are to use the test approach I suggested (which does not have use a local variable) you DO need to replace the yellow bits with your list, and your table goes where "mytable" is referenced

;WITH
  D1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),
  D2(N) AS (SELECT 1 FROM D1 a, D1 b),
  D4(N) AS (SELECT 1 FROM D2 a, D2 b)
, Numbers AS (
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM D4
)
, Missing AS (
SELECT
  SUBSTRING('y,i,for,xyz,abc',N.Number+1,CHARINDEX(',','y,i,for,xyz,abc'+',',N.Number+1)-N.Number-1) AS Data
   FROM Numbers n
  WHERE N.Number < LEN('y,i,for,xyz,abc')
    AND SUBSTRING('y,i,for,xyz,abc',N.Number,1) = ','
)
SELECT Missing.data as Missing_values
FROM Missing
LEFT JOIN Mytable ON Missing.data = Mytable.Column1
WHERE Mytable.Column1 IS NULL
0
 
Pawan KumarDatabase ExpertCommented:
try this/...

SELECT [all_columns]
FROM tableName
WHERE colName NOT IN ('y','i','xyz')

Open in new window


Enjoy !!
0
 
Ryan ChongCommented:
what's your database (you have mentioned MS SQL Server and Access in your question tag) and how this list is being passed to your database?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
lluddenCommented:
This is a fairly common problem.  A following is a general solution that can be adapted to parse any delimited text into rows.

--Create sample table
CREATE TABLE Test(ID INT IDENTITY(1,1), Column1 VARCHAR(30))
INSERT INTO Test (Column1) VALUES ('y');
INSERT INTO Test (Column1) VALUES ('xyz');
INSERT INTO Test (Column1) VALUES ('i');

--Parameter being passed
DECLARE @Parameter VARCHAR(MAX) =  'y,i,for,xyz,abc'

--===== Add start and end commas to the Parameter so we can handle single elements
    SET @Parameter = ','+@Parameter +','

--Normally, I'd have this table predefined, but you can generate it on the fly
;WITH
  D1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),
  D2(N) AS (SELECT 1 FROM D1 a, D1 b),
  D4(N) AS (SELECT 1 FROM D2 a, D2 b)
, Numbers AS (
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM D4
) 
--Numbers contains a list of numbers from 1-1000.  Will handle up to 1000 items in the delimited list.  
-- Adjust larger or smaller depending upon your needs

--Join the Numbers table to the string at the character level and
-- when we find a comma, select what's between that and the next comma
, ParsedData AS (
 SELECT SUBSTRING(@Parameter,N.Number+1,CHARINDEX(',',@Parameter,N.Number+1)-N.Number-1) AS Data
   FROM Numbers n
  WHERE N.Number < LEN(@Parameter)
    AND SUBSTRING(@Parameter,N.Number,1) = ',' 
)
SELECT * FROM ParsedData
WHERE DATA NOT IN (SELECT Column1 FROM Test)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
If we are splitting then we can directly join the ParsedData with the Table and get the records where ID is null.

NOT In will be slow if the list is long.
0
 
bfuchsAuthor Commented:
@Pawan,
I need the opposite

something like select 'y','i',xyz' where..not in table.mycolumn..

@Ryan,
I have Access app linked to SQL, therefore either one would work.
The list is something copied from a document, not saved in DB.

Thanks,
Ben
0
 
Pawan KumarDatabase ExpertCommented:
Ok Try this ..

DECLARE @x AS XML=''
DECLARE @Param AS VARCHAR(100) = 'y,i,xyz'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

SELECT * FROM 
(
	SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
)k
OUTER APPLY
(
	SELECT * From yourtable b
	WHERE b.[columne] = k.value
)
WHERE k.Value IS NULL

Open in new window

0
 
Ryan ChongCommented:
I have Access app linked to SQL, therefore either one would work.
The list is something copied from a document, not saved in DB.
so I guess perhaps you can construct a SQL as simple as what Pawan Kumar Khowal mentioned in ID: 41819090, but what you need to do is to build a dynamic query in your Access form with some macro scripts.

or you may adapt other methods suggested by other experts that tackle the issue from MS SQL side.
0
 
bfuchsAuthor Commented:
@lludden
I got the following with yours.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@Parameter".
Msg 137, Level 15, State 2, Line 26
Must declare the scalar variable "@Parameter".

Maybe because my SQL version is 2005?

Thanks,
Ben
0
 
bfuchsAuthor Commented:
@Pawan,

Same error msg by yours

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@Param".
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@x".
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'WHERE'.

How do I perform these in SQL 2005?

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Hi Experts,

I'm living work now, please continue posting & will resume testing tom.

Thanks,
Ben
0
 
GhunaimaCommented:
Select * from
(Select 'y' lst union select 'i' lst union 'for' lst union 'xyz' lst union 'abc' lst) a where  lst not in (select mytable.column1 from mytable)
0
 
Pawan KumarDatabase ExpertCommented:
@Ghunaima - We should union all if we know that values are different. Other wise it will distinct the resultset.
Also your query has syntax errors :). Check out "union all 'for' lst"

Easiest would be...

--

Select * from
(Select 'y' lst union all select 'i' lst union all SELECT 'for' lst union all SELECT 'xyz' lst union all SELECT 'abc' lst) a
EXCEPT
(select mytable.column1 from mytable) b

--

Open in new window


Or may be

Select * from
(Select 'y' lst union all select 'i' lst union all SELECT 'for' lst union all SELECT 'xyz' lst union all SELECT 'abc' lst) a
WHERE a.ist NOT IN
(select mytable.column1 from mytable) 

Open in new window

0
 
PaulCommented:
The basic approach is to transform the list into a table.
Then join that to your existing table via an outer join.
A null value produced from the join tells you which items in the list do not exist in the current table.
0
 
PaulCommented:
with apologies to lluden, a small rewrite of that:
DECLARE @List AS VARCHAR(200) = '''y'',''i'',''for'',''xyz'',''abc'''

--select @List

;WITH
  D1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),
  D2(N) AS (SELECT 1 FROM D1 a, D1 b),
  D4(N) AS (SELECT 1 FROM D2 a, D2 b)
, Numbers AS (
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM D4
) 
, Missing AS (
SELECT
  SUBSTRING(@List,N.Number+1,CHARINDEX(',',@List+',',N.Number+1)-N.Number-1) AS Data
   FROM Numbers n
  WHERE N.Number < LEN(@List)
    AND SUBSTRING(@List,N.Number,1) = ',' 
)
SELECT replace(Missing.data,'''','') as Missing_values
FROM Missing
LEFT JOIN (
          select 'y' as column1 union all
          select 'xyz' as column1 union all
          select 'i' as column1
          ) Mytable ON replace(Missing.data,'''','') = Mytable.Column1
WHERE Mytable.Column1 IS NULL

Open in new window

result:
Missing_values
----- 
for
abc

(2 row(s) returned)

Open in new window

nb  line 18 of the query @List+',' adding the trailing comma avoids a negative number and hence an error when using substring

I used a small unioning subquery to produce the equivalent of "mytable"
0
 
PaulCommented:
Sorry was over doing the string didnt need all those commas
DECLARE @List AS VARCHAR(200) = 'y,i,for,xyz,abc'

--select @List

;WITH
  D1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),
  D2(N) AS (SELECT 1 FROM D1 a, D1 b),
  D4(N) AS (SELECT 1 FROM D2 a, D2 b)
, Numbers AS (
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM D4
) 
, Missing AS (
SELECT
  SUBSTRING(@List,N.Number+1,CHARINDEX(',',@List+',',N.Number+1)-N.Number-1) AS Data
   FROM Numbers n
  WHERE N.Number < LEN(@List)
    AND SUBSTRING(@List,N.Number,1) = ',' 
)
SELECT Missing.data as missing_values
FROM Missing
LEFT JOIN (
          select 'y' as column1 union all
          select 'xyz' as column1 union all
          select 'i' as column1
          ) Mytable ON Missing.data = Mytable.Column1
WHERE Mytable.Column1 IS NULL

Open in new window

0
 
ZberteocCommented:
The simplest query you can come up with for this would be:
declare
	@list varchar(8000)='y,i,for,xyz,abc'


select 
	@list_not_in_table=replace(@list+',',vals.column1+',','')
from
(
	select distinct 
		column1
	from 
		mytable
) vals


select @list_not_in_table as list_not_in_table

Open in new window

0
 
ZberteocCommented:
Sorry, the script above needs some corrections and a test with data sample:
if object_id('tempdb..#mytable') is not null
	drop table #mytable
create table #mytable (column1 varchar(50))
insert into #mytable select col from (values ('y'),('xyz'),('i'))v(col)

declare
	@list varchar(8000)='y,i,for,xyz,abc'
declare
	@list_not_in_table varchar(8000)=@list+','


select 
	@list_not_in_table=replace(@list_not_in_table,vals.column1,'')
from
(
	select distinct 
		column1+',' as column1
	from 
		#mytable
) vals


-- get rid of the last comma
select @list_not_in_table=left(@list_not_in_table,len(@list_not_in_table)-1) 

-- results
select @list as list, @list_not_in_table as list_not_in_table

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Try this..

0. If you are passing a string.

DECLARE @x AS XML=''
DECLARE @Param AS VARCHAR(100) = 'y,i,xyz'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

SELECT * FROM 
(
	SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
)k
OUTER APPLY
(
	SELECT * From yourtable b
	WHERE b.[columne] = k.value
)
WHERE k.Value IS NULL

Open in new window

1. Using EXCEPT

--
Select * from
(Select 'y' lst union all select 'i' lst union all SELECT 'for' lst union all SELECT 'xyz' lst union all SELECT 'abc' lst) a
EXCEPT
(select mytable.column1 from mytable) b

Open in new window


--

2. USING NOT IN

Select * from
(Select 'y' lst union all select 'i' lst union all SELECT 'for' lst union all SELECT 'xyz' lst union all SELECT 'abc' lst) a
WHERE a.ist NOT IN
(select mytable.column1 from mytable) 

Open in new window


3. USING LEFT JOIN

Select * from
(Select 'y' lst union all select 'i' lst union all SELECT 'for' lst union all SELECT 'xyz' lst union all SELECT 'abc' lst) a
LEFT JOIN
(select mytable.column1 from mytable) x ON x.column1 = a.lst
WHERE x.column1 IS NULL 

Open in new window


Enjoy!!
0
 
bfuchsAuthor Commented:
Hi Experts,

Just arrived at work..

Will start testing & let U know.

PS, are you addressing the issue that my version is 2005?

Thanks,
Ben
0
 
bfuchsAuthor Commented:
@Paul,

Had the same issue as previous suggestions, cannot assign value to local variable.

@ Zberteoc,
this is what I got from your latest.

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'values'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@list".
Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@list_not_in_table".
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'vals'.
Msg 137, Level 15, State 2, Line 24
Must declare the scalar variable "@list_not_in_table".
Msg 137, Level 15, State 2, Line 27
Must declare the scalar variable "@list".

As of now seems like Ghunaima and pawan's options of union are the way to go in my version..

Just wondering, is there a way to have that list transformed by the SQL statement itself as a union (as in reality the list is quite long, would prefer a copy & paste method), perhaps using something like replace function?

Thanks,
Ben
0
 
PaulCommented:
The local variable is an example method
You have provided very little detail.

How are you executing the sql? Through what tool?
0
 
ZberteocCommented:
What SQL server version do you have? Works perfectly here. Maybe you used the code before I edited it. Here it is again:
if object_id('tempdb..#mytable') is not null
	drop table #mytable
create table #mytable (column1 varchar(50))
insert into #mytable select col from (values ('y'),('xyz'),('i'))v(col)

declare
	@list varchar(8000)='y,i,for,xyz,abc'
declare
	@list_not_in_table varchar(8000)=@list+','


select 
	@list_not_in_table=replace(@list_not_in_table,vals.column1,'')
from
(
	select distinct 
		column1+',' as column1
	from 
		#mytable
) vals


-- get rid of the last comma
select @list_not_in_table=left(@list_not_in_table,len(@list_not_in_table)-1) 

-- results
select @list as list, @list_not_in_table as list_not_in_table


(3 row(s) affected)
list                      list_not_in_table
------------------------- -------------------------
y,i,for,xyz,abc           for,abc

Open in new window

0
 
PaulCommented:
here is a varant with the list hardcoded, so no local variable is used, just as a test.
;WITH
  D1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),
  D2(N) AS (SELECT 1 FROM D1 a, D1 b),
  D4(N) AS (SELECT 1 FROM D2 a, D2 b)
, Numbers AS (
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM D4
) 
, Missing AS (
SELECT
  SUBSTRING('y,i,for,xyz,abc',N.Number+1,CHARINDEX(',','y,i,for,xyz,abc'+',',N.Number+1)-N.Number-1) AS Data
   FROM Numbers n
  WHERE N.Number < LEN('y,i,for,xyz,abc')
    AND SUBSTRING('y,i,for,xyz,abc',N.Number,1) = ',' 
)
SELECT Missing.data as Missing_values
FROM Missing
LEFT JOIN (
          select 'y' as column1 union all
          select 'xyz' as column1 union all
          select 'i' as column1
          ) Mytable ON Missing.data = Mytable.Column1
WHERE Mytable.Column1 IS NULL

Open in new window

0
 
PaulCommented:
What is the relevance of SharePoint to this question? Are you trying to execute through SharePoint?
0
 
bfuchsAuthor Commented:
@Paul,
How are you executing the sql? Through what tool?
I can use whatever method you suggest..currently using SSMS 2008 linked to a 2005 DB.

What is the relevance of SharePoint to this question?
Looks like EE changed their site and some things get pre-entered in the topics area as I did not select that..

anyway will test yours & Zberteoc's then get back.

Thanks,
Ben
0
 
bfuchsAuthor Commented:
@Zberteoc,

got below

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'tempdb..#mytable'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'values'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@list".
Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@list_not_in_table".
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'vals'.
Msg 137, Level 15, State 2, Line 24
Must declare the scalar variable "@list_not_in_table".
Msg 137, Level 15, State 2, Line 27
Must declare the scalar variable "@list".

As mentioned I have SSMS 2005.

Actually my pc has version 2008 its just the server where the DB resides has 2005.

I may also try with a downloaded version of 2014 if that matters..

Thanks,
Ben
0
 
bfuchsAuthor Commented:
@Paul,

Your latest seems to work.

Now I need instructions how to apply to my situation, if you dont mind..

Besides of replacing everywhere where you have 'y,i,for,xyz,abc' with the contents of my list, what else do I have to do?

and where do I place my table (which is select documentname from documenttypes)

Thanks,
Ben
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Have you tried mine ?
0
 
bfuchsAuthor Commented:
Hi Pawan,

As I said above (ID: 41820527), yours would also work, however has an issue in constructing the union all for each element.

Actually with paul's it looks I will face the same problem.

Let me know if you can have a solution to that within the SQL statement.

Thanks,
Ben
0
 
Pawan KumarDatabase ExpertCommented:
can you post the issue you are facing in my query. Shall fix it right away.
0
 
bfuchsAuthor Commented:
@Pawan,

Can you post the full SQL I would use given the following details?

My list is 'acls,all,for,xyz,BCLS,CoreMandatory,Application form,Chauncey'
my table is select documentname from documenttypes

@Paul,

Your latest works well, just to be fair I'm waiting for Pawan to respond on this in order to finalize.

Thanks,
Ben
0
 
ZberteocCommented:
Actually my pc has version 2008 its just the server where the DB resides has 2005.
That is the problem. 2005 doesn't have that syntax for VALUES with multiple rows. This will work:
if object_id('tempdb..#mytable') is not null
	drop table #mytable
create table #mytable (column1 varchar(50))
insert into #mytable values ('y')
insert into #mytable values ('xyz')
insert into #mytable values ('i')

declare
	@list varchar(8000)='y,i,for,xyz,abc'
declare
	@list_not_in_table varchar(8000)=@list+','


select 
	@list_not_in_table=replace(@list_not_in_table,vals.column1,'')
from
(
	select distinct 
		column1+',' as column1
	from 
		#mytable
) vals


-- get rid of the last comma
select @list_not_in_table=left(@list_not_in_table,len(@list_not_in_table)-1) 

-- results
select @list as list, @list_not_in_table as list_not_in_table

Open in new window

0
 
bfuchsAuthor Commented:
@Zberteoc,

See attached..

Thanks,
Ben
Untitled.png
0
 
ZberteocCommented:
Sorry, assignment in declare also doesn't work in 2005. Try this:
if object_id('tempdb..#mytable') is not null
	drop table #mytable
create table #mytable (column1 varchar(50))
insert into #mytable values ('y')
insert into #mytable values ('xyz')
insert into #mytable values ('i')

declare
	@list varchar(8000),
	@list_not_in_table varchar(8000)

set @list='y,i,for,xyz,abc'
set @list_not_in_table=@list+','


select 
	@list_not_in_table=replace(@list_not_in_table,vals.column1,'')
from
(
	select distinct 
		column1+',' as column1
	from 
		#mytable
) vals


-- get rid of the last comma
select @list_not_in_table=left(@list_not_in_table,len(@list_not_in_table)-1) 

-- results
select @list as list, @list_not_in_table as list_not_in_table

Open in new window

0
 
bfuchsAuthor Commented:
OK that returns no errors.

What do I have to do besides changing the following with my list?

set @list='y,i,for,xyz,abc'

Thanks,
Ben
0
 
ZberteocConnect With a Mentor Commented:
You remove that part at the top with the temporary table and inserts and then replace the #mytable with your actual table and column1 with your actual column name:
declare
	@list varchar(8000),
	@list_not_in_table varchar(8000)

set @list='y,i,for,xyz,abc'
set @list_not_in_table=@list+','


select 
	@list_not_in_table=replace(@list_not_in_table,vals.WorkValues,'')
from
(
	select distinct 
		column1+',' as WorkValues -- replace column1 here with the real column name; I changed the alias to WorkValues to be used above so don't need to change that
	from 
		#mytable -- replace #mytable here with the real table name
) vals


-- get rid of the last comma
select @list_not_in_table=left(@list_not_in_table,len(@list_not_in_table)-1) 

-- results
select @list as list, @list_not_in_table as list_not_in_table

Open in new window

0
 
bfuchsAuthor Commented:
@Zberteoc,

That also works great!

As mentioned, will give some time for Pawan in order to split the points fairly..

Thanks,
Ben
0
 
Pawan KumarDatabase ExpertCommented:
Hi Ben,
Pls find the solutions below-

/*
** SOLUTION 1 - USING APPLY Operator
*/

---

IF OBJECT_ID('TEMPDB..#MYTABLE') IS NOT NULL
	DROP TABLE #MYTABLE
CREATE TABLE #MYTABLE (COLUMN1 VARCHAR(50))
INSERT INTO #MYTABLE VALUES ('Y')
INSERT INTO #MYTABLE VALUES ('XYZ')
INSERT INTO #MYTABLE VALUES ('I')

DECLARE @x AS XML=''
DECLARE @Param AS VARCHAR(100) = 'y,i,for,xyz,abc'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

DECLARE @list_not_in_table AS VARCHAR(1000) = ''

SELECT @list_not_in_table = @list_not_in_table + Value + ',' FROM 
(
	SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
)k
OUTER APPLY
(
	SELECT * From #mytable b
	WHERE b.column1 = k.value
)z
WHERE z.column1 IS NULL

---

Open in new window



/*
** SOLUTION 2 - USING EXCEPT OPERATOR
*/

---


IF OBJECT_ID('TEMPDB..#MYTABLE') IS NOT NULL
	DROP TABLE #MYTABLE
CREATE TABLE #MYTABLE (COLUMN1 VARCHAR(50))
INSERT INTO #MYTABLE VALUES ('Y')
INSERT INTO #MYTABLE VALUES ('XYZ')
INSERT INTO #MYTABLE VALUES ('I')

DECLARE @x AS XML=''
DECLARE @Param AS VARCHAR(100) = 'y,i,for,xyz,abc'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

DECLARE @list_not_in_table AS VARCHAR(1000) = ''

SELECT @list_not_in_table = @list_not_in_table + Value + ','
FROM
(
	SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
	EXCEPT
	SELECT COLUMN1 FROM #MYTABLE
)r

SELECT @Param RecordsIntheList , SUBSTRING(@list_not_in_table, 1, NULLIF(DATALENGTH(@list_not_in_table)-1,-1)) ListNotInTable

---

Open in new window



/*
** SOLUTION 3 - USING NOT IN
*/

---


IF OBJECT_ID('TEMPDB..#MYTABLE') IS NOT NULL
	DROP TABLE #MYTABLE
CREATE TABLE #MYTABLE (COLUMN1 VARCHAR(50))
INSERT INTO #MYTABLE VALUES ('Y')
INSERT INTO #MYTABLE VALUES ('XYZ')
INSERT INTO #MYTABLE VALUES ('I')

DECLARE @x AS XML=''
DECLARE @Param AS VARCHAR(100) = 'y,i,for,xyz,abc'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

DECLARE @list_not_in_table AS VARCHAR(1000) = ''

SELECT @list_not_in_table = @list_not_in_table + Value + ','
FROM
(
	SELECT * FROM 
	(
		SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
	)x
	WHERE Value NOT IN ( SELECT COLUMN1 FROM #MYTABLE )
)r

SELECT @Param RecordsIntheList , SUBSTRING(@list_not_in_table, 1, NULLIF(DATALENGTH(@list_not_in_table)-1,-1)) ListNotInTable


---

Open in new window


/*
** SOLUTION 4 - USING LEFT JOIN
*/

---


IF OBJECT_ID('TEMPDB..#MYTABLE') IS NOT NULL
	DROP TABLE #MYTABLE
CREATE TABLE #MYTABLE (COLUMN1 VARCHAR(50))
INSERT INTO #MYTABLE VALUES ('Y')
INSERT INTO #MYTABLE VALUES ('XYZ')
INSERT INTO #MYTABLE VALUES ('I')

DECLARE @x AS XML=''
DECLARE @Param AS VARCHAR(100) = 'y,i,for,xyz,abc'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

DECLARE @list_not_in_table AS VARCHAR(1000) = ''

SELECT @list_not_in_table = @list_not_in_table + Value + ','
FROM
(
	SELECT * FROM 
	(
		SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
	)x
	LEFT JOIN ( SELECT COLUMN1 FROM #MYTABLE )t ON t.COLUMN1 = x.Value
	WHERE t.COLUMN1 IS NULL
)r

SELECT @Param RecordsIntheList , SUBSTRING(@list_not_in_table, 1, NULLIF(DATALENGTH(@list_not_in_table)-1,-1)) ListNotInTable


---

Open in new window


I Hope it helps

Enjoy!!

Pawan
0
 
bfuchsAuthor Commented:
Hi Pawan,

You forgot about the 2005 issue..

they all return with following error

Cannot assign a default value to a local variable.

Thanks,
Ben
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Ohhh.. yes , Pls find the updated code..

/*
** SOLUTION 1 - USING APPLY Operator
*/

---

IF OBJECT_ID('TEMPDB..#MYTABLE') IS NOT NULL
	DROP TABLE #MYTABLE
CREATE TABLE #MYTABLE (COLUMN1 VARCHAR(50))
INSERT INTO #MYTABLE VALUES ('Y')
INSERT INTO #MYTABLE VALUES ('XYZ')
INSERT INTO #MYTABLE VALUES ('I')

DECLARE @x AS XML

DECLARE @Param AS VARCHAR(100)

SET @Param = 'y,i,for,xyz,abc'

SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

DECLARE @list_not_in_table AS VARCHAR(1000)

SET @list_not_in_table = ''

SELECT @list_not_in_table = @list_not_in_table + Value + ',' FROM 
(
	SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
)k
OUTER APPLY
(
	SELECT * From #mytable b
	WHERE b.column1 = k.value
)z
WHERE z.column1 IS NULL

SELECT @Param RecordsIntheList , SUBSTRING(@list_not_in_table, 1, NULLIF(DATALENGTH(@list_not_in_table)-1,-1)) ListNotInTable

---

Open in new window



/*
** SOLUTION 2 - USING EXCEPT OPERATOR
*/

---


IF OBJECT_ID('TEMPDB..#MYTABLE') IS NOT NULL
	DROP TABLE #MYTABLE
CREATE TABLE #MYTABLE (COLUMN1 VARCHAR(50))
INSERT INTO #MYTABLE VALUES ('Y')
INSERT INTO #MYTABLE VALUES ('XYZ')
INSERT INTO #MYTABLE VALUES ('I')

DECLARE @x AS XML

DECLARE @Param AS VARCHAR(100)

SET @Param = 'y,i,for,xyz,abc'

SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

DECLARE @list_not_in_table AS VARCHAR(1000)

SET @list_not_in_table = ''

SELECT @list_not_in_table = @list_not_in_table + Value + ','
FROM
(
	SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
	EXCEPT
	SELECT COLUMN1 FROM #MYTABLE
)r

SELECT @Param RecordsIntheList , SUBSTRING(@list_not_in_table, 1, NULLIF(DATALENGTH(@list_not_in_table)-1,-1)) ListNotInTable

---

Open in new window



/*
** SOLUTION 3 - USING NOT IN
*/

---


IF OBJECT_ID('TEMPDB..#MYTABLE') IS NOT NULL
	DROP TABLE #MYTABLE
CREATE TABLE #MYTABLE (COLUMN1 VARCHAR(50))
INSERT INTO #MYTABLE VALUES ('Y')
INSERT INTO #MYTABLE VALUES ('XYZ')
INSERT INTO #MYTABLE VALUES ('I')

DECLARE @x AS XML

DECLARE @Param AS VARCHAR(100)

SET @Param = 'y,i,for,xyz,abc'

SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

DECLARE @list_not_in_table AS VARCHAR(1000)

SET @list_not_in_table = ''

SELECT @list_not_in_table = @list_not_in_table + Value + ','
FROM
(
	SELECT * FROM 
	(
		SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
	)x
	WHERE Value NOT IN ( SELECT COLUMN1 FROM #MYTABLE )
)r

SELECT @Param RecordsIntheList , SUBSTRING(@list_not_in_table, 1, NULLIF(DATALENGTH(@list_not_in_table)-1,-1)) ListNotInTable


---

Open in new window


/*
** SOLUTION 4 - USING LEFT JOIN
*/

---


IF OBJECT_ID('TEMPDB..#MYTABLE') IS NOT NULL
	DROP TABLE #MYTABLE
CREATE TABLE #MYTABLE (COLUMN1 VARCHAR(50))
INSERT INTO #MYTABLE VALUES ('Y')
INSERT INTO #MYTABLE VALUES ('XYZ')
INSERT INTO #MYTABLE VALUES ('I')

DECLARE @x AS XML

DECLARE @Param AS VARCHAR(100)

SET @Param = 'y,i,for,xyz,abc'

SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

DECLARE @list_not_in_table AS VARCHAR(1000)

SET @list_not_in_table = ''

SELECT @list_not_in_table = @list_not_in_table + Value + ','
FROM
(
	SELECT * FROM 
	(
		SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
	)x
	LEFT JOIN ( SELECT COLUMN1 FROM #MYTABLE )t ON t.COLUMN1 = x.Value
	WHERE t.COLUMN1 IS NULL
)r

SELECT @Param RecordsIntheList , SUBSTRING(@list_not_in_table, 1, NULLIF(DATALENGTH(@list_not_in_table)-1,-1)) ListNotInTable


---

Open in new window


Enjoy!!

Pawan
0
 
bfuchsAuthor Commented:
Thank you experts, we finally got 3 working solutions
Great Job!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.