Using the IN keyword in an SQL select

[david7777]

I know you can use the IN keyword in a select, but can't remember how...

ie:
[CODE]
SELECT * FROM products WHERE id IN [1, 23, 53, 12, 97]
[CODE]

Is that right? How should it be done?

[raf]

It will probably be 'correct'. That is; you have two kind a "in"'s.
- "In" = operator --> what you need here
- "IN" = clause --> which needs to be used when you refer to the results of a subquery ot to an (extrernal) table/database

I don't know if the lower-uppercase makes any difference (since i always use the right form ;) ) but it's probably not a bad idea to use the right form.

Just to be overcomplete, some motre info on both

Info from helpfile:
---------------------
IN Clause
Identifies tables in any external database to which the Microsoft Jet database engine can connect, such as a dBASE or Paradox database or an external Microsoft® Jet database.

Syntax
To identify a destination table:

[SELECT | INSERT] INTO destination IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

To identify a source table:

FROM tableexpression IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

A SELECT statement containing an IN clause has these parts:

Part Description
destination The name of the external table into which data is inserted.
tableexpression The name of the table or tables from which data is retrieved. This argument can be a single table name, a saved query, or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.
path The full path for the directory or file containing table.
type The name of the database type used to create table if a database is not a Microsoft Jet database (for example, dBASE III, dBASE IV, Paradox 3.x, or Paradox 4.x).


Remarks
You can use IN to connect to only one external database at a time.

In some cases, the path argument refers to the directory containing the database files. For example, when working with dBASE, Microsoft FoxPro®, or Paradox database tables, the path argument specifies the directory containing .dbf or .db files. The table file name is derived from the destination or tableexpression argument.

To specify a non-Microsoft Jet database, append a semicolon (;) to the name, and enclose it in single (' ') or double (" ") quotation marks. For example, either 'dBASE IV;' or "dBASE IV;" is acceptable.

You can also use the DATABASE reserved word to specify the external database. For example, the following lines specify the same table:

... FROM Table IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];

... FROM Table IN "C:\DBASE\DATA\SALES" "dBASE IV;"



--------------------------------------------------------------------------------

Notes

For improved performance and ease of use, use a linked table instead of IN.

You can also use the IN reserved word as a comparison operator in an expression. For more information, see the In operator.


Info 2 from helpfile:
----------------------------
In Operator
Determines whether the value of an expression is equal to any of several values in a specified list.

Syntax
expr [Not] In(value1, value2, . . .)

Remarks
The In operator syntax has these parts:

Part Description
expr Expression identifying the field that contains the data you want to evaluate.
value1, value2 Expression or list of expressions against which you want to evaluate expr.


If expr is found in the list of values, the In operator returns True; otherwise, it returns False. You can include the Not logical operator to evaluate the opposite condition (that is, whether expr is not in the list of values).

For example, you can use In to determine which orders are shipped to a set of specified regions:

SELECT *

FROM Orders

WHERE ShipRegion In (‘Avon’,’Glos’,’Som’)

[david7777]

Thanx - thats exactly what i was looking for!