Solved

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

Posted on 2016-09-27
42
111 Views
Last Modified: 2016-09-29
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'
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 9
  • 7
  • +4
42 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41819090
try this/...

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

Open in new window


Enjoy !!
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41819091
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
 
LVL 18

Expert Comment

by:lludden
ID: 41819093
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41819097
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41819098
@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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41819102
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
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41819105
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41819107
@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
 
LVL 4

Author Comment

by:bfuchs
ID: 41819136
@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
 
LVL 4

Author Comment

by:bfuchs
ID: 41819138
Hi Experts,

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

Thanks,
Ben
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 41819145
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41819148
@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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41819615
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41819674
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41819728
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41819773
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41819799
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41819815
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41820486
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41820527
@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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41820671
The local variable is an example method
You have provided very little detail.

How are you executing the sql? Through what tool?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41820702
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41820749
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41820768
What is the relevance of SharePoint to this question? Are you trying to execute through SharePoint?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41820873
@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
 
LVL 4

Author Comment

by:bfuchs
ID: 41820882
@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
 
LVL 4

Author Comment

by:bfuchs
ID: 41820896
@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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41821000
@Author - Have you tried mine ?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41821065
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41821077
can you post the issue you are facing in my query. Shall fix it right away.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 41821175
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41822507
@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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41822518
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41822537
@Zberteoc,

See attached..

Thanks,
Ben
Untitled.png
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41822547
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41822571
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
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 125 total points
ID: 41822587
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41822601
@Zberteoc,

That also works great!

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

Thanks,
Ben
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41822860
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41822881
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
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 125 total points
ID: 41822892
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
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 41822900
Thank you experts, we finally got 3 working solutions
Great Job!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question