Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Query to determine what value isn't found in a query result

Posted on 2010-01-07
7
Medium Priority
?
279 Views
Last Modified: 2012-05-08
I have a list of values example: ('a','b','c','d') and have to determine which of those values are not in a SQL query  result - example: "SELECT thisValue From DTable"

Is there a way to do that?

Thank you.


0
Comment
Question by:RichardKline
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26203724
SELECT thisValue From DTable  where Value not in  ('a','b','c','d')
0
 
LVL 10

Expert Comment

by:lof
ID: 26203797
If you have only few values and you want to enumerate them in the query solution proposed by aneeshattingal will do.

However if you have another table, say DTableX (for eXcept) then you may use EXCEPT

SELECT thisValue FROM DTable
EXCEPT
SELECT thisValue FROM DTableX
0
 
LVL 3

Expert Comment

by:roeib
ID: 26203837
use a split function like this:

CREATE FUNCTION [dbo].[SPLIT] (

@str_in VARCHAR(8000),

@separator VARCHAR(4) )

RETURNS @strtable TABLE (strval VARCHAR(8000))

AS

BEGIN

DECLARE

@Occurrences INT,

@Counter INT,

@tmpStr VARCHAR(8000)

SET @Counter = 0

IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator

SET @str_in = @str_in + @separator

SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)

SET @tmpStr = @str_in

WHILE @Counter <= @Occurrences

BEGIN

SET @Counter = @Counter + 1

INSERT INTO @strtable

VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

IF DATALENGTH(@tmpStr) = 0

BREAK

END

RETURN

END

 
then you can simply run the select using it

select * from DTTable where Value not in (select strVal from Split('a','b','c'))
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 1

Author Comment

by:RichardKline
ID: 26204078
I may have mis-explained.   I need the inverse of SELECT thisValue From DTable  where Value not in  ('a','b','c','d') ,

Use pseudocode, something like:
 SELECT thisValue FROM ('a','b','c','d')  WHERE value NOT IN SELECT thatvalue From DTable  

Thank you.
0
 
LVL 10

Accepted Solution

by:
lof earned 2000 total points
ID: 26204179
then you should go for EXCEPT

if you don't want to create extra table you may do it like that

select * from (
     select 'a' value
     union all select 'b'
     union all select 'c'
     union all select 'd'
) T
EXCEPT
select thatValue from DTable

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26204182

 SELECT ThisValue
 FROM (SELECT 'a' as thisValue UNION SELECT 'B' UNION SELECT 'C' UNION Select 'D' ) a
 WHERE NOT EXISTS  (SELECT 1 from DTable where thatValue = a.thisValue )
 
0
 
LVL 1

Author Closing Comment

by:RichardKline
ID: 31674211
Thanks!
aneeshattingal 's answer may work as well but lof was quicker on understanding what I needed and his syntax seems cleaner.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

577 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