Discussion:
HELP with SELECT IN CLAUSE
(too old to reply)
John Cosmas
2005-06-15 16:28:00 UTC
Permalink
I need to execute a SELECT statement that has an IN clause; but it needs to
behave like NOT IN. My current statement looks like

SELECT * FROM Company WHERE Company.Name IN "A"

I need it to act like WHERE Company.Name NOT IN "A", so is this a valid
statement??
Jack Jackson
2005-06-15 17:22:05 UTC
Permalink
On Wed, 15 Jun 2005 11:28:00 -0500, "John Cosmas"
Post by John Cosmas
I need to execute a SELECT statement that has an IN clause; but it needs to
behave like NOT IN. My current statement looks like
SELECT * FROM Company WHERE Company.Name IN "A"
I need it to act like WHERE Company.Name NOT IN "A", so is this a valid
statement??
The syntax of the IN clause is IN(<list of values>), so your first
statement should be:
SELECT * FROM Company WHERE Company.Name IN("A").

If you have SET ANSI OFF, records whose name field begin with A will
be returned.

You can put NOT in front of the IN:

SELECT ... WHERE Company.Name NOT IN ("A")

To use more standard SQL you could use LIKE:

SELECT ... WHERE Company.Name LIKE "A%"
John Cosmas
2005-06-15 17:35:54 UTC
Permalink
Thanks, Jack;

How do I run a SELECT COUNT(*) in code and pass the results into a variable?

John
Post by Jack Jackson
On Wed, 15 Jun 2005 11:28:00 -0500, "John Cosmas"
Post by John Cosmas
I need to execute a SELECT statement that has an IN clause; but it needs to
behave like NOT IN. My current statement looks like
SELECT * FROM Company WHERE Company.Name IN "A"
I need it to act like WHERE Company.Name NOT IN "A", so is this a valid
statement??
The syntax of the IN clause is IN(<list of values>), so your first
SELECT * FROM Company WHERE Company.Name IN("A").
If you have SET ANSI OFF, records whose name field begin with A will
be returned.
SELECT ... WHERE Company.Name NOT IN ("A")
SELECT ... WHERE Company.Name LIKE "A%"
Fred Taylor
2005-06-16 01:03:07 UTC
Permalink
You can either use COUNT FOR condition TO variable, or SELECT COUNT(*) FROM
table INTO ARRAY arrayname.

Arrayname element 1 would have the count in it.
--
Fred
Microsoft Visual FoxPro MVP
Post by John Cosmas
Thanks, Jack;
How do I run a SELECT COUNT(*) in code and pass the results into a variable?
John
Post by Jack Jackson
On Wed, 15 Jun 2005 11:28:00 -0500, "John Cosmas"
Post by John Cosmas
I need to execute a SELECT statement that has an IN clause; but it needs to
behave like NOT IN. My current statement looks like
SELECT * FROM Company WHERE Company.Name IN "A"
I need it to act like WHERE Company.Name NOT IN "A", so is this a valid
statement??
The syntax of the IN clause is IN(<list of values>), so your first
SELECT * FROM Company WHERE Company.Name IN("A").
If you have SET ANSI OFF, records whose name field begin with A will
be returned.
SELECT ... WHERE Company.Name NOT IN ("A")
SELECT ... WHERE Company.Name LIKE "A%"
newboy
2005-08-11 08:24:21 UTC
Permalink
you can also use the _tally variable to get the records count after you use
the SELECT or other SQL statment.
Post by Fred Taylor
You can either use COUNT FOR condition TO variable, or SELECT COUNT(*) FROM
table INTO ARRAY arrayname.
Arrayname element 1 would have the count in it.
--
Fred
Microsoft Visual FoxPro MVP
Post by John Cosmas
Thanks, Jack;
How do I run a SELECT COUNT(*) in code and pass the results into a variable?
John
Post by Jack Jackson
On Wed, 15 Jun 2005 11:28:00 -0500, "John Cosmas"
Post by John Cosmas
I need to execute a SELECT statement that has an IN clause; but it
needs
Post by Fred Taylor
Post by John Cosmas
Post by Jack Jackson
Post by John Cosmas
to
behave like NOT IN. My current statement looks like
SELECT * FROM Company WHERE Company.Name IN "A"
I need it to act like WHERE Company.Name NOT IN "A", so is this a valid
statement??
The syntax of the IN clause is IN(<list of values>), so your first
SELECT * FROM Company WHERE Company.Name IN("A").
If you have SET ANSI OFF, records whose name field begin with A will
be returned.
SELECT ... WHERE Company.Name NOT IN ("A")
SELECT ... WHERE Company.Name LIKE "A%"
Loading...