PDOXDOS40 Query problem 
Author Message
 PDOXDOS40 Query problem

Hi,
I was having a problem in doing a query. The problem is like this:
I have two tables with identical structures, let's say the only difference
between them is that have data sampled at different time. So, they can have
some data same, but there are some records which are in table A but not in
table B, and some records which are in table B but not in A. So I want to
query out all the records which are in table A but not in table B and vice
versa. Something like not of linking them with an example element.
Could somebody help me here
Thanks a lot
Dhawal.

---
Home Address:               Office Address:
424 North Main St., Apt #E  1700, Pratt Dr., #209
Blacksburg, VA 24060.       Information Systems Building
TEL#:   (703)951-3979       Corporate Research Center
                            Blacksburg, VA 24061
                            TEL#:   (703)231-3905, 231-8398.

``If you can't understand something, you don't give up. You must think harder!''
                                                --Harish Chandra



Wed, 21 Aug 1996 04:08:07 GMT
 PDOXDOS40 Query problem

: Hi,
: I was having a problem in doing a query. The problem is like this:
: I have two tables with identical structures, let's say the only difference
: between them is that have data sampled at different time. So, they can have
: some data same, but there are some records which are in table A but not in
: table B, and some records which are in table B but not in A. So I want to
: query out all the records which are in table A but not in table B and vice
: versa. Something like not of linking them with an example element.
: Could somebody help me here
: Thanks a lot
: Dhawal.

: ---
: Home Address:               Office Address:
: 424 North Main St., Apt #E  1700, Pratt Dr., #209
: Blacksburg, VA 24060.       Information Systems Building
: TEL#: (703)951-3979       Corporate Research Center
:                             Blacksburg, VA 24061
:                             TEL#:   (703)231-3905, 231-8398.

: ``If you can't understand something, you don't give up. You must think harder!''
:                                               --Harish Chandra
What I would do:

   -Copy table A to a new table C.
   -Delete all items of B from the table C by running a Delete query.

Sample of a Delete query:

Query

 ANSWER: :PRIV:ANSWER.DB

 NAMES2.DB | Nbr   | Nbr2  | LName | Name  |
           | _EG01 | _EG02 | _EG03 | _EG04 |

 NAMES3.DB | Nbr   | Nbr2  | LName | Name  |
 Delete    | _EG01 | _EG02 | _EG03 | _EG04 |

EndQuery

PRIV:DELETED Gets all the deleted stuff, which in this case is same as
all of NAMES2.DB = your table B. What is left in NAMES3 = your table C is
A-B.


Have Fun!                                                          Wilhelm



Thu, 22 Aug 1996 04:35:19 GMT
 PDOXDOS40 Query problem
Thanks to everybody who responded to my query problem, I'm posting the solution for
everybody else' benefit.

Dhawal

----Included Doc-----

  PRODUCT  :  Paradox for Windows                   NUMBER  :  1435
  VERSION  :  All
       OS  :  WIN
     DATE  :  October 26, 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:

  CUSTOMERKMCust #MKMMLast NameMMKMMFirst NameMM;
       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       :

  ORDERSMKMOrder #MKMMMMDateMMMKMMCust #MKMPartMMKMMPriceMM;
       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  :  All
       OS  :  WIN
     DATE  :  October 26, 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.

  CUSTOMERKMCust #MKMMLast NameMMKMMFirst NameMM;
          : { _c!  :             :              :

  ORDERSMKMOrder #MKMMMMDateMMMKMMCust #MMMMMKMPartMMKMMPriceMM;
         :         :           : _c, count=0 :       :         :

  ANSWERMKMCust #M;
       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  :  All
       OS  :  WIN
     DATE  :  October 26, 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.

---
Home Address:               Office Address:
424 North Main St., Apt #E  1700, Pratt Dr., #209
Blacksburg, VA 24060.       Information Systems Building
TEL#:   (703)951-3979       Corporate Research Center
                            Blacksburg, VA 24061
                            TEL#:   (703)231-3905, 231-8398.

``If you can't understand something, you don't give up. You must think harder!''
                                                --Harish Chandra



Fri, 23 Aug 1996 10:02:56 GMT
 PDOXDOS40 Query problem

Quote:

>Hi,
>I was having a problem in doing a query. The problem is like this:
>I have two tables with identical structures, let's say the only difference
>between them is that have data sampled at different time. So, they can have
>some data same, but there are some records which are in table A but not in
>table B, and some records which are in table B but not in A. So I want to
>query out all the records which are in table A but not in table B and vice
>versa. Something like not of linking them with an example element.
>Could somebody help me here

From memory (haven't the manuals handy): (Can any of you real experts jump in
and present a real solution)

I think you need the ! operator (inclusion operator) in one of your query forms.
That table then will be the master table from which every record is matched;
you link it with the second table and set a condition that filters out
every record matched from the second. Sorry i'm somewhat vague. Reference
the Paradox User's guide about Ask or Advanced queries.

Good luck!

--
  ---.    /)        |                      
   /  \  /    _  _  |   _   _  _                 Dion Nicolaas

_/___/  \|  -------------------'



Sat, 24 Aug 1996 17:19:54 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. PDOXDOS40: Why doesn't F10 key work?

2. PDOXDOS40: F1 for help in app: how?

3. PDOXDOS40: F1 for help in app: how?

4. PDOXDOS40: WHY DOESN'T F10 KEY WORK?

5. Converting Access query to SQL 2K Query Problems

6. Problem Querying Linked server from outside Query Analyzer

7. XML query node problem: same level root from output xml query

8. Local sql, query on query problem

9. Problem with query to Oracle 8 database using Oracle ODBC with Excel and Microsoft Query

10. SQL2000:query problems on query analyzer

11. Pervasive SQL Queries (Query problems)

12. Problem Cancelling Queries In BI/Query


 
Powered by phpBB® Forum Software