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 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
try this/...

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

Open in new window


Enjoy !!
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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?
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

Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

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

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
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
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
bfuchsAuthor Commented:
Hi Experts,

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

Thanks,
Ben
G Trurab KhanSnr. Development ManagerCommented:
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)
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

PortletPaulEE Topic AdvisorCommented:
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.
PortletPaulEE Topic AdvisorCommented:
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"
PortletPaulEE Topic AdvisorCommented:
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

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

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

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!!
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
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
PortletPaulEE Topic AdvisorCommented:
The local variable is an example method
You have provided very little detail.

How are you executing the sql? Through what tool?
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

PortletPaulEE Topic AdvisorCommented:
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

PortletPaulEE Topic AdvisorCommented:
What is the relevance of SharePoint to this question? Are you trying to execute through SharePoint?
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
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
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
Pawan KumarDatabase ExpertCommented:
@Author - Have you tried mine ?
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
Pawan KumarDatabase ExpertCommented:
can you post the issue you are facing in my query. Shall fix it right away.
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
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

bfuchsAuthor Commented:
@Zberteoc,

See attached..

Thanks,
Ben
Untitled.png
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

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
ZberteocCommented:
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

bfuchsAuthor Commented:
@Zberteoc,

That also works great!

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

Thanks,
Ben
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
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
Pawan KumarDatabase 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
bfuchsAuthor Commented:
Thank you experts, we finally got 3 working solutions
Great Job!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.