DevGuru Jet SQL SELECT Statement


			STATEMENT: SELECT SELECT [predicate] { * \| table.* \| [table.]field1 [AS alias1] [, table.]field2 [AS alias2] [, ...] ] } FROM tableexpression [, ...] [IN externaldatabase] [WHERE... ] [NOT] [IN] (value1,[value2,[...] ] ) [GROUP BY... ] [HAVING... ] [ORDER BY... ] [WITH OWNERACCESS OPTION] [subqueryclause [subqueryclause [...] ] ] The SELECT statement returns information from a database as a set of records without altering the database. It searches the database, extracts the chosen columns and selects those records that meet the criteria, sorting and grouping the results into a specified order. A SELECT statement can be nested inside of another SELECT statement which is nested inside of another SELECT and so on. When a SELECT is nested it is refered to as a subquery clause. At its simplest, you can use an asterisk ( * ) to select all of the fields in a table (in this case the table 'Musicians' ): SELECT * FROM Musicians; Or you can be more selective and choose just one or a few of the fields in a table, and they will be returned in the order listed: SELECT MusicianName, Instrument FROM Musicians; You can also have a query display a field under a different heading than the one used in the table by establishing an "alias" with the reserved word AS: SELECT MusicianName AS Musician, Instrument FROM Musicians; ...and you can even have a query combine two or more fields from a table into one field in the returned list under a new heading using the ampersand character: SELECT Name, City & ", " & Country AS Location FROM Customers; WHERE You can use the WHERE clause to further focus your selection by specifying certain criteria to be met by the values. The following example returns the names of all musicians who play the flute: SELECT MusicianName AS Musician FROM Musicians WHERE Instrument = 'flute'; ...and this example returns the names of all jigs in a 'Tunes' table: SELECT TuneName AS Name, Source1 AS Recording FROM Tunes WHERE TuneType = 'jig'; You can combine more than one criterion in a WHERE clause using any of the logical operators. Here the query returns a list of all items which are blue and cost less than $100: SELECT Item, UnitPrice AS Price FROM Products WHERE Color = 'blue' AND UnitPrice < 100; The optional, reserved word IN can be used either as a clause or as an operator. If you want to get fields from a database other than the one you are currently working in, you use the IN as a clause: SELECT Name, Address FROM PianoTuners IN USAPiano.mdb WHERE state='TN'; If the database is a non-Microsoft Jet database, you must append a semicolon ( ; ) to the database file name and enclose it within a pair of single ( ' ) or double quotes ( " ). SELECT Name, Address FROM PianoTuners IN "InstrumentTuners.xls;" WHERE state='TN'; You can also specify a path and designate the type of file. Note the position of the ( ; ): SELECT Name, Address FROM PianoTuners IN "C:\Music\InstrumentTuners.xls" "Excel 5.0;" WHERE state='TN'; When used as an operator, IN can determine if the values of a specified expression matches any values in a specified list. This example determines if any piano tuners live in 'Knoxville', 'Nashville', or 'Memphis'. A pair of single quotes must enclose each value and commas must separate each value: SELECT * FROM TennPianoTuners WHERE City IN ( 'Knoxville', 'Nashville', 'Memphis' ); You can also add a NOT. This causes the query to select all values other than those listed: SELECT * FROM TennPianoTuners WHERE City NOT IN ( 'Knoxville', 'Nashville', 'Memphis' ); The SELECT statement can optionally be followed by one of these four predicates: ALL, DISTINCT, DISTINCTROW, TOP. These limit the number of records returned. The ALL predicate is the default, but it is rarely used. Note that the following two code examples yield the exact same results: SELECT * FROM RockAndRoll WHERE Artist = 'Elvis'; SELECT ALL * FROM RockAndRoll WHERE Artist = 'Elvis'; The DISTINCT predicate is used to omit duplicate values just in a field. Consider a table of names, where you have the last name, "Jones", repeated numerous times. This code returns only one "Jones": SELECT DISTINCT LastName FROM SongWriters; The DISTINCTROW predicate is used to omit duplicate values in an entire record of fields. This can be very useful when you use a INNER JOIN to join two tables together and you do not want any duplication. This code create a table that does not repeat any of the last names: SELECT DISTINCTROW LastName FROM SongWriters INNER JOIN Performers ORDER BY LastName; The TOP predicate returns the specified number of records from the top of the specified table. The following example returns the first 3 records: SELECT TOP 3 MusicianName AS Musician, Instrument FROM Musicians; You can also carry out calculations on fields containing numeric values using the aggregate functions: AVG - average COUNT - count how many items MAX - maximum value MIN - minimum value STDEV - sample standard deviation STDEVP - standard deviation SUM - add the values VAR - sample variance VARP - variance This next example uses the COUNT function to count the number of items that have an entry in the SalePrice field (i.e. they are on sale) and returns that number under the heading 'ReducedCount': SELECT COUNT(SalePrice) AS ReducedCount FROM Products; ...and this next one returns current prices along with what the prices would be after a 10% increase: SELECT Item, UnitPrice AS CurrentPrice, UnitPrice * 1.1 AS IncreasedPrice FROM Products; ...and this one lists all items that are reduced along with the price and the amount of the reduction: SELECT Item, SalePrice AS Price, UnitPrice - SalePrice AS Reduction FROM Products WHERE SalePrice <> Null; Of course, you may want to select fields from more than one table, and you can do that as well. In this case it is best to precede a field name with the name of the table from which it comes, followed by the dot operator ( . ). You must do this for fields of the same name, but from different tables that are used in the SELECT statement. The following example uses two tables, Task and Assignment, and returns the names of all Tasks belonging to Assignments that are incomplete: SELECT Task.Name, Task.TaskID FROM Task INNER JOIN Assignment ON Task.TaskID = Assignment.TaskID WHERE Assignment.CompletionDate Is Null; As an alternative to using the explicit INNER JOIN syntax, columns from multiple tables can be combined in a single query by specifying the appropriate table list, and applying the filter condition in the WHERE clause. This is illustrated in the following query, which returns the same recordset as the previous example: SELECT Task.Name, Task.TaskID FROM Task, Assignment WHERE Task.TaskID = Assignment.TaskID AND Assignment.CompletionDate Is Null; GROUP BY The optional GROUP BY clause groups into a single record all records that have identical values in a particular field or combination of fields. The following example returns a list of the different products in the Product field of Suppliers. SELECT Product FROM Suppliers GROUP BY Product; HAVING The HAVING clause is optional and qualifies a GROUP BY clause. It is similar to the WHERE clause but determines which records are displayed after they have been grouped. The following example displays a list of different items, along with their count, but only where there are more than one. SELECT Item, Count(Item) AS Tally FROM Products GROUP BY Item HAVING Count(Item) > 1; ORDER BY The ORDER BY clause can be used to dictate the order of the records returned. The following example returns records listed primarily in order of tune type (jigs then reels), and then for each type the relevant names are also listed in alphabetical order. SELECT TuneType AS Type, Name FROM Tunes WHERE TuneType = 'jig' OR TuneType = 'reel' ORDER BY TuneType, Name; WITH OWNERACCESS OPTION In a multi-user environment utilizing secure workgroups, the WITH OWNERACCESS OPTION declaration allows the query to be executed with the same permissions as the owner of the query.