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

try this/...

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

Select allOpen in new window

Enjoy !!

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?

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)

Select allOpen in new window

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.

@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

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

Select allOpen in new window

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.

@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

@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

Hi Experts,

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

Thanks,
Ben

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)

@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

--

Select allOpen 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) 

Select allOpen in new window

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.

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

Select allOpen in new window

result:

Missing_values
----- 
for
abc

(2 row(s) returned)

Select allOpen 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"

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen 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

Select allOpen 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) 

Select allOpen 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 

Select allOpen in new window

Enjoy!!

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

@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

The local variable is an example method
You have provided very little detail.

How are you executing the sql? Through what tool?

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

Select allOpen in new window

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

Select allOpen in new window

What is the relevance of SharePoint to this question? Are you trying to execute through SharePoint?

@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

@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

@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

@Author - Have you tried mine ?

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

can you post the issue you are facing in my query. Shall fix it right away.

@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

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

Select allOpen in new window

@Zberteoc,

See attached..

Thanks,
Ben
Untitled.png

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

Select allOpen in new window

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

@Zberteoc,

That also works great!

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

Thanks,
Ben

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

---

Select allOpen 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

---

Select allOpen 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


---

Select allOpen 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


---

Select allOpen in new window

I Hope it helps

Enjoy!!

Pawan

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

Thank you experts, we finally got 3 working solutions
Great Job!