>When there are nulls in the list, ANSI SQL-92 will always return the empty set. Rather than allowing the NOT IN syntax and risking this type of "surprise" result, FoxPro does not support the NOT IN syntax.
>
>To test for values that are NOT IN the list one should use the syntax:
>
>... WHERE NOT EXISTS ;
> (SELECT * FROM MyTable WHERE MyTable.Value = ValueImLookingFor)
>
>Randall was asking how to test for values that are "IN" not values that are "NOT IN".
OK since we're on the subject here...
Whenever I have a situation like Randall's or Nadya's where I want to check for existence/non-existence of a value in a list, I
always try to work it out with a subquery like Cindy has shown here with either WHERE EXISTS or WHERE NOT EXISTS as appropriate. The reason being is both the SQL IN clause and VFP's INLIST function basically boil down to hardcoding.
Back in VFP 5 I tried to get both INLIST() and IN() to work with variable values and never did get to work out. Anybody else know of a way to use either of these without hardcoding the list of values they use?
Roxanne M. Seibert
Independent Consultant, VFP MCP
Code Monkey Like Fritos