This clause works in conjunction with the WHERE clause to determine which rows of data to include.
Options
For a description of the options, see SELECT.
About the Sub-SELECT Clause
A sub-SELECT command, which is a SELECT command nested within another command, always appears in a WHERE clause, whether the sub-SELECT command is nested in the WHERE clause of a SELECT command or in another command such as EDIT USING form. R:BASE processes the clauses in a sub-SELECT in the same order as in a SELECT command. The order (ascending/descending) is ignored within a Sub-SELECT. All control of ordering should be in the outermost SELECT.
Examples
The following example selects customers that have purchased items in the month of March within the ConComp sample database:
SELECT company FROM customer WHERE custid +
IN (SELECT custid FROM transmaster +
WHERE (IMON(transdate)=3))
company
PC Distribution Inc.
Computer Distributors Inc.
Industrial Concepts Inc.
PC Consultation and Design
The following example selects and groups employee and customer IDs for purchases sold by the employee Ernest Hernandez, within the ConComp sample database
SELECT empid, custid FROM transmaster WHERE empid IN +
(SELECT empid FROM Employee WHERE EmpLName = 'Hernandez') +
GROUP BY empid, custid
empid custid
---------- ----------
129 105
129 110
129 113
129 117
129 124