not(field1 in ('value1', 'value2', ...)) but return value1

[ttdobj]

So if I create the following query:

select t2.field1 from t1 right join t2
on t1.field1 = t2.field1
where isnull(t1.field1, 'xxx') = 'xxx'

If I have written this right, this should return all those values in t2.field1 that are not in t1.field1

But what if instead of a table called t2, I just had a list of values. And the values were presented like you would if you were doing:
field1 in ('value1', value2', 'value3')

Or put another way:

I want to check if any of value1, value2, value3 are in table1, and if they are not in table1 then to return whichever of value1, value2 or value3 are not in the table1

I am trying to avoid having to create tables on the fly, cause I don't want to give that web page permissions to create tables. ASP if you are wondering.

And it seems a waste to have to check each value individually.

Can anyone help?

regards

John

 

[Gixonita]

Hi,

I would change your first query to:

SELECT t2.field1 

FROM t2 

RIGHT JOIN t1
     ON t1.field1 = t2.field1

WHERE t1.field1 IS NULL

I don't see the need to use the ISNULL function.

Your second issue is a little difficult to understand since you contradict yourself :

I want to check if any of value1, value2, value3 are in table1, and if they are not in table1 then to return whichever of value1, value2 or value3 are not in the table1

If the values are not in the table then the query can't possibly return them. Taking into account the title of the post then maybe what you are looking for is

SELECT t2.field1 

FROM t2

WHERE t2.field1 NOT IN (value1, value2, value3)

Hope this helps.

 

[ttdobj]

I have come across a solution to this problem
the key to it being:

SELECT Reference = 'value1'
UNION ALL SELECT 'value2'
UNION ALL SELECT 'value3'
UNION ALL SELECT 'value4'

so the full query would be:

SELECT t2.field1
FROM (SELECT field1 = 'value1' UNION ALL SELECT 'value2' UNION ALL SELECT 'value3') t2
WHERE NOT EXISTS ( SELECT 1 FROM t1 WHERE t1.field1 = t2.field1)