Solved

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

Posted on 2016-09-27
42
92 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
  • 16
  • 9
  • 7
  • +4
42 Comments
 
LVL 17

Expert Comment

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

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

Open in new window


Enjoy !!
0
 
LVL 49

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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
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 3

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 17

Expert Comment

by:Pawan Kumar Khowal
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 49

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 3

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 3

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 3

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 17

Expert Comment

by:Pawan Kumar Khowal
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 48

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 48

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 48

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 26

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 26

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 17

Expert Comment

by:Pawan Kumar Khowal
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 3

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 3

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 48

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

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 48

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 48

Expert Comment

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

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 3

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 3

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 17

Expert Comment

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

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 17

Expert Comment

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

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 3

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 26

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 3

Author Comment

by:bfuchs
ID: 41822537
@Zberteoc,

See attached..

Thanks,
Ben
Untitled.png
0
 
LVL 26

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 3

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 26

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 3

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 17

Expert Comment

by:Pawan Kumar Khowal
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 3

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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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 3

Author Closing Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now