Usage

SELECT [ ALL | DISTINCT ]
       [ TOP nHowMany [ PERCENT ] ]
        eColumn1 [ AS ColumnName1 ]
             [, eColumn2 [ AS ColumnName2 ] ... ] 
       FROM [ FORCE ]
            [ Database1! ]Table1 [ [ AS ] LocalAlias1 ]
              [ [ INNER | LEFT [ OUTER ] | RIGHT [ OUTER ]
               | FULL [ OUTER ] ] JOIN
               [ Database2! ]Table2 [ [ AS ] LocalAlias2 ] 
                 [ ... ] 
               [ ON lJoinCondition1 ] 
               | , [ Database3! ]Table3 [ [ AS ] LocalAlias3 ] 
                 [ ... ] ]
        [ WHERE lConditions ]
        [ GROUP BY GroupColumn1 [, GroupColumn2 ... ] ]
        [ HAVING lGroupFilter ]
        [ UNION [ ALL | DISTINCT ] SELECT ... ]
        [ ORDER BY OrderCriteria1 [ ASC | DESC ] 
              [, OrderCriteria2 [ ASC | DESC ] ... ] ]
        [INTO CURSOR CursorName [ NOFILTER ] [ READWRITE ]
         | INTO TABLE | DBF TableName 
         | INTO ARRAY ArrayName 
         | TO FILE FileName [ ADDITIVE ] 
         | TO PRINTER [ PROMPT ] 
         | TO SCREEN ] 
       [ PREFERENCE PreferenceName ]
       [ NOCONSOLE ]
       [ PLAIN ]
       [ NOWAIT ]

Parameter

Value

Meaning

nHowMany

Numeric

The number of records or percentage of the records to include in the final result.

eColumnx

Expression

An expression showing how to compute the data to go into the xth column of the result. Fields in the expression may be aliased. The expression should be written so that all values are the same length (in characters).

ColumnNamex

Name

A name to assign to the xth field in the result set. Follows all the usual FoxPro field naming rules.

Databasex

Name

The database containing the xth table in the query.

Tablex

Name

The xth table to join into the query.

LocalAliasx

Name

An alias to use within the query for the xth table in the FROM clause.

lJoinConditionx

Logical

An expression (most likely, a comparison) that provides the join conditions for the xth join listed.

lConditions

Logical

The conditions used to choose the records that go into the result set. There are two types of conditions: join conditions involving fields from more than one table (though joins can also be handled in the FROM clause) and filter conditions involving fields from a single table.

GroupColumnx

Numeric

The number of the column in the result that is to be used as the xth grouping criterion.

Name

The column name of the column to be used as the xth grouping criterion.

lGroupFilter

Logical

An expression on which to filter the results of grouping. This condition is applied to the intermediate results (normally following GROUPing) of the query.

OrderCriteriax

Numeric

The number of the column in the result to use as the xth ordering criterion.

Name

The name of a column to use as the xth ordering criterion.

CursorName

Name

A name for the cursor containing the result set.

TableName

Name

A name for the table containing the result set.

ArrayName

Array

An array to contain the result set.

FileName

Name

The name of a file into which a list of the result set should be dumped. The format is the same as using the LIST command.

PreferenceName

Name

A name for a BROWSE Preference to be used or created for the results. This is useful only when results go to the default Browse. You need an active resource file to do this.

Several old bugs were fixed in the service packs to VFP 6 and in VFP 7, so that SELECT's results are more accurate. If you have a query or view whose results have changed between versions, check out the ReadMe file for VFP 7.

Example

* Compute the number of 1994 orders for each customer
* in the TasTrade customer list, but keep only those with 
* 10 or more. Note that you can't use the TOP clause
* for this.
SELECT Customer.Customer_ID, COUNT(*) ;
   FROM TasTrade!Customer ;
     JOIN TasTrade!Orders ;
       ON Customer.Customer_ID=Orders.Customer_ID ;
   WHERE YEAR(Orders.Order_Date)=1994 ;
   GROUP BY 1 ;
   HAVING COUNT(*)>=10 ;
   INTO CURSOR OrderCount
 
* Find all the customers with no orders.
SELECT * FROM Customer ;
  WHERE Customer_ID NOT IN (SELECT Customer_ID FROM Orders) 
 
* Join Orders with Customer, Employee and Shipper.
* Here's the nested syntax - does the order of the joins
* make sense to you? It doesn't to us.
SELECT Customer.company_name, ;
       Orders.order_date, ;
       Employee.last_name,;
       Shippers.company_name AS Shipper_Name;
 FROM tastrade!customer ;
   JOIN tastrade!employee;
     JOIN tastrade!shippers ;
       JOIN tastrade!orders ;
       ON Shippers.shipper_id = Orders.shipper_id ;
     ON Employee.employee_id = Orders.employee_id ;
   ON Customer.customer_id = Orders.customer_id
 
* Here's the same query using the sequential syntax.
* We think it's a lot more readable.
SELECT Customer.company_name, ;
       Orders.order_date, ;
       Employee.last_name,;
       Shippers.company_name AS Shipper_Name ;
 FROM tastrade!orders ;
   JOIN tastrade!customer ;
     ON Customer.customer_id = Orders.customer_id ;
   JOIN tastrade!employee  ;
     ON Employee.employee_id = Orders.employee_id ;
   JOIN tastrade!shippers ;
     ON Shippers.shipper_id = Orders.shipper_id
 
* Here's an example where the nested syntax makes sense
* because the tables have a hierarchical relationship.
SELECT Company_Name, Order_Date, Product_Name ;
 FROM Customer ;
   JOIN Orders ;
     JOIN Order_Line_Items ;
       JOIN Products ; 
         ON Order_Line_Items.Product_Id = Products.Product_Id ;
     ON Orders.Order_Id = Order_Line_Items.Order_Id ;
   ON Customer.Customer_Id = Orders.Customer_Id
 
 
* What if we want to see all the customers in that query?
* We need an outer join between Customer and Orders.
SELECT Company_Name, Order_Date, Product_Name ;
 FROM Customer ;
   LEFT JOIN Orders ;
     JOIN Order_Line_Items ;
       JOIN Products ; 
         ON Order_Line_Items.Product_Id = Products.Product_Id ;
       ON Orders.Order_Id = Order_Line_Items.Order_Id ;
     ON Customer.Customer_Id = Orders.Customer_Id

See Also

ASort(), Between(), Browse, Create Cursor, Create SQL View, InList(), Like(), Set ANSI