Tutorial:Programming
database. If we want a listing of unique
Where we are dealing with numeric
The result of this query in MSAc
names then how can we generate it?
data we may only be interested in a
cess is shown in Figure 17).
SQL includes a number of predi
subset based on the position of the
We might equally have wanted to
cates , which specify which records
results, for example, the top 10 records,
see a list of employees who do not
the query is to return. In all of our
or the bottom 20% and so on. The
work in development. In this case our
examples so far we have assumed that
predicate for this is TOP, and differs
WHERE statement can be amended to:
we want all of the records to be re
from the previous ones in that it de
turned, which is equivalent to includ
pends on your query to order the re
WHERE Dept<> Development
ing the ALL predicate with our
sults so that it can pick out the relevant
statements.
records.
Multiple WHERE statements can be
If we want a listing of unique names
created to fine tune our queries and
then we can use the DISTINCT predi
The WHERE Clause
select very precise subsets of our data.
cate to exclude those records which are
For example, if we wanted to look at
not unique based on the SELECTed
So far all the queries we have
those employees not in development
fields in the query. In other words the
looked at operate on the complete con
but who have Internet access, we can
query becomes:
tents of a table. What if you are only
code the following query:
interested in a particular subset of a
SELECT DISTINCT LastName
table? The WHERE keyword is used to
SELECT *
FROM UserTable
provide a search mechanism which
FROM UserTable
only produces records which meet a
WHERE Dept<> Development
In other cases we may only want to
given search criterion. For example, if
And Internet=True
exclude records where the entire re
we are only interested in our users in
cord is duplicated, not just a given field
the Development department, we
The WHERE command can also be
or set of fields. To do this we use the
would modify one of our earlier com
used with arithmetic and logical opera
DISTINCTROW predicate. In our da
mands as follows:
tors. For example, if we wanted all those
tabase there are no records in the
employees whose surnames begin with
UserTable which are duplicates, so us
SELECT *
the letter J (which you might want when
ing DISTINCTROW will return all the
FROM UserTable
producing a company contact list):
records in the table.
WHERE Dept= Development
SELECT *
FROM UserTable
SELECT DISTINCT UserTable.FirstName, UserTable.LastName,
WHERE LastName Like J*
Software.ProductName
FROM (UserSoftware INNER JOIN UserTable ON UserSoftware.EmpNo =
Or perhaps you want to select
UserTable.EmpNo) INNER JOIN Software ON UserSoftware.
names in the group A M:
ProductID = Software.ProductID;
WHERE LastName Between A*
Figure 19 SELECT query with multiple inner joins.
And M*
For a full list of legal WHERE crite
FirstName
LastName
ProductName
ria refer to the documentation for your
Bharat
Patel
VB6
particular implementation of SQL.
Jim
Jones
VB5
Jim
Jones
VB6
Mary
Jones
Autoroute Plus
Sorting Results
Mary
Jones
MS Access
Where a query might produce
Mary
Jones
MS Word
Terry
Jones
PaintShop Pro
many records it is often useful to sort
Terry
Jones
VB6
the result by a given field. SQL uses the
ORDER BY keyword to do this. If we
wanted to order our UserTable by
Figure 20 Result of query in Figure 19.
LastName, we would create the fol
lowing query:
SELECT DISTINCT UserTable.FirstName, UserTable.LastName,
Software.ProductName, PC.PCTypeName
SELECT *
FROM ((UserSoftware INNER JOIN UserTable ON UserSoftware.EmpNo =
FROM UserTable
UserTable.EmpNo) INNER JOIN Software ON UserSoftware.ProductID =
ORDER BY LastName
Software.ProductID) INNER JOIN PC ON UserTable.PCType = PC.PCTypeName;
Or, if we wanted the table sorted by
LastName within Department, the
Figure 21 Multiple JOINs in a SELECT query.
File: T1125.8
Issue 109 (July/August 1999) page 20
PC Network Advisor
< Next page >
New! The best sites for quality inkjet printer cartridges and the best sites for cheap inkjet cartridges