home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Monster Media 1993 #2
/
Image.iso
/
database
/
p4w_all.zip
/
TI1435.ASC
< prev
next >
Wrap
Text File
|
1993-04-14
|
5KB
|
199 lines
PRODUCT : Paradox for Windows NUMBER : 1435
VERSION : 1.0
OS : WIN
DATE : April 14, 1993 PAGE : 1/3
TITLE : Doing "Not-in" Queries
Intended Audience
All Paradox users
Prerequisites
Familiarity with queries
Chapters 6 and 7, User's Guide
Purpose of the TI
This document discusses how to perform a query which will select
the records from one table that do not exist in another.
It is often desirable to select records from one table that do
not exist in another table. This type of query is often called a
"Not-in" query (Which records from one table are "not in"
another?). The following example uses two tables "Customer" and
"Orders" to illustrate how to perform a "Not-in" query.
Suppose that you have two tables, "Customer" and "Orders" as
follows:
CUSTOMER╦═Cust #═╦══Last Name══╦══First Name══╗
1 ║ 1015 ║ Elkins ║ Eugene ║
2 ║ 1020 ║ Fenton ║ Mark ║
3 ║ 1055 ║ Harding ║ Jim ║
4 ║ 1056 ║ Wilden ║ Marc ║
5 ║ 1319 ║ Fenton ║ Teresa ║
6 ║ 1341 ║ Goves ║ Cynthia ║
7 ║ 1342 ║ Goves ║ Joseph ║
8 ║ 1685 ║ Murray ║ Yvonne ║
9 ║ 1988 ║ Martinez ║ Susan ║
10 ║ 2022 ║ Staebell ║ Alice ║
ORDERS═╦═Order #═╦════Date═══╦══Cust #═╦═Part══╦══Price══╗
1 ║ 1 ║ 7/9/89 ║ 1015 ║ DR ║ 46.20 ║
2 ║ 2 ║ 7/9/89 ║ 1341 ║ WR ║ 3.96 ║
3 ║ 3 ║ 7/28/89 ║ 1988 ║ HA ║ 12.04 ║
4 ║ 4 ║ 8/3/89 ║ 1988 ║ GH ║ 14.52 ║
5 ║ 5 ║ 8/4/89 ║ 1341 ║ DR ║ 46.20 ║
6 ║ 6 ║ 8/14/89 ║ 1055 ║ VI ║ 65.93 ║
7 ║ 7 ║ 8/15/89 ║ 1020 ║ DR ║ 46.20 ║
8 ║ 8 ║ 8/16/89 ║ 1341 ║ GH ║ 14.52 ║
9 ║ 9 ║ 8/18/89 ║ 1015 ║ TB ║ 33.66 ║
PRODUCT : Paradox for Windows NUMBER : 1435
VERSION : 1.0
OS : WIN
DATE : April 14, 1993 PAGE : 2/3
TITLE : Doing "Not-in" Queries
The query shown below asks the question "Which customers have
never placed an order?". This query links the Customer table and
the Orders table with an example element (c), and uses the
inclusion operator (!) in the Cust # field of the Customer table.
The inclusion operator tells Paradox to include every customer
number from the Customer table, regardless of whether it exists
in the Orders table. The Orders table contains the expression
"count=0" in the Cust # field. This tells Paradox to count the
number of occurrences of each customer number in the Orders
table, and only include the customer numbers that occur zero
times.
NOTE: The underscore character "_" represents an example element.
To create an example element, press [F5] (or type an
underscore "_")_, then type the characters you want to use.
The example element will be highlighted.
CUSTOMER╦═Cust #═╦══Last Name══╦══First Name══╗
║ √ _c! ║ ║ ║
ORDERS═╦═Order #═╦════Date═══╦══Cust #═════╦═Part══╦══Price══╗
║ ║ ║ _c, count=0 ║ ║ ║
ANSWER═╦═Cust #═╗
1 ║ 1056 ║
2 ║ 1319 ║
3 ║ 1342 ║
4 ║ 1685 ║
5 ║ 2022 ║
The resulting Answer table shows that customer numbers 1056,
1319, 1342, 1685, and 2022 have not placed orders.
To help understand how this query works, try changing the
"count=0" in the Cust # field of the Orders table to "calc count
all". When you run the query, the resulting Answer table will
have 10 records, since there are 10 records in the Customer
table. The "Count of Cust #" field will show the number of times
each customer appears in the Orders table. The records for
PRODUCT : Paradox for Windows NUMBER : 1435
VERSION : 1.0
OS : WIN
DATE : April 14, 1993 PAGE : 3/3
TITLE : Doing "Not-in" Queries
customer numbers 1056, 1319, 1342, 1685, and 2022 show a count of
zero. These same customer numbers appear in the Answer table for
the "Not-in query".
The information in this document came from Brian J. Smith's
article "How to Ask 'Not-In' Queries" which first appeared in the
October 1989 issue of "Instant Scripts", a publication of the
LAPALS user group in Southern California.
Suggested Reading:
Chapters 6 and 7, User's Guide
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.