Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Newsgroups  |  Paradox Web Sites  |  Paradox Book List  |  FAQs From The Corel FAQ Newsgroup  



Subject: TIP:PdoxWin:Not-in Queries:1999:09.26

Version 1.1 (1999.09.26)
Reposted by Mike Irwin: 2001.02.10


====================
0. Introduction
====================
A "not-in" query is something that many applications need 
to do. Basically, you can think of it as a "what did I 
forget" query !

Thanks to Liz W. (corrected by Liz from original FAQ) for corrections.

-------------------------------
 0.1 Legal Info and Disclaimers
-------------------------------
Paradox is a trademark of Corel.
Borland Database Engine (BDE) is a trademark of Inprise.
The information provided in this document is provided "as 
is" and is not warranted in any way. It is not endorsed or
authorized by Corel or Inprise in any shape, form, or 
manner.

The editors claim NO responsibility for ANY illegal 
activity regarding this file, or as a result of someone 
reading this file.

You may distribute this file, as long as the copies are
complete, unaltered, and are in electronic form only.

-------------
 0.2 Feedback
-------------
Note: The NewsGroup and EMail software sometimes combine to
remove blank lines from listings, which can mean that 
queries and SQL code will not run unless you replace the 
blank lines !

Please send feedback in a Corel Paradox newsgroup or the

news:comp.databases.Paradox

newsgroup, to any of the FAQ Team mentioned in the
"FAQ: FAQ FAQ" document. Please preface the subject of your
post with the string "PDXWIN TIP" to alert Team members to
the function of the message.

Please specify the TIP name and section number the
comment applies to, if any.

=======================================
1. Subject. Not-in Queries
=======================================

Using SQL.
==============

This type of query asks the question "what have I not 
done". For example, given a list of all the patients in a 
ward, and a list of injections given, which patients have 
not received a rubella injection ?

1. Single table:
==============

This is really an example of bad design, as you should not
be including the types of immunisation in the patient 
record.

---------------------
select p.PatientName
from Patients p
where p.Rubella is NULL
---------------------

2. Two tables,
==============

Here, the where the omission is recorded in the detail 
table. You _should_ have a Master:Detail relationship here 
where Details are attributes of the patient, and a Rubella
injection is a kind of attribute 

Thus we're looking for Master records where a certain type 
of Detail record doesn't exist. The algorithm here is to 
select all the patients who have had Rubella injections 
(the sub-Select) and then select all patients except these.

---------------------
select p.Name
from Patients p
where p.PatientId NOT IN  
            (select pi.PatientId
             from PatientImmunisations pi
             where pi.ImmunisationType = "Rubella"
            )
---------------------

While it is quite possible to do this with a set of QBE
queries, it is lots easier in SQL.

Using QBE.
==============

In QBE there are at least two radically different ways:

1. Subtraction. This is the easiest to understand, but
longest to code and costliest in cpu time to execute.

2. Outer Join. This is more difficult to understand, but
shorter to code.

Note: Contrary to some expectations, the Outer Join query 
can easily be far faster than the Subtraction method, 
despite the fact that it operates on the whole dataset.


1. Subtraction
==============
In QBE, you have to go through the following steps:
1. Make a list of all possible patients, from the Patients
   table
2. Make a list of all patients that have had Rubella
   injections
3. Subtract one table from the other to end up with the
   "not in" list.
4. Use that list to obtain the data you want from the 
   original list of patients.

Here, for example, are the queries. Each is numbered to
correspond with the lines above.

1. ====================================
Query
ANSWER: :PRIV:PossiblePatients.DB
Patients.DB | PatientId |
            | Check     |
EndQuery

2. ====================================
Query
ANSWER: :PRIV:HadRubella.DB
PatientImmunisations.DB | PatientId | ImmunisationType |
                        | Check     | Rubella          |
EndQuery

3. ====================================
Query
ANSWER: :PRIV:ANSWER.DB
:PRIV:HadRubella.DB | PatientId |
                    | _join1    |
:PRIV:PossiblePatients.DB | PatientId |
Delete                    | _join1    |
EndQuery

4. ====================================
Query
ANSWER: :PRIV:ANSWER.DB
Patients.DB | PatientId    | Name  |
            | Check _join1 | Check |
:PRIV:PossiblePatients.DB | PatientId |
                          | _join1    |
EndQuery
====================================



2. Outer Join
=============

This is a lot easier to do than #1 above but somewhat more
complicated to understand.

1. The tables are joined, of course, on PatientID

2. The Outer Join or "Inclusion" marker (the "!", which 
   cannot be separated by any character from the join 
   identifier) ensures that those patients without any 
   immunisation records at all are also listed, with a 
   value of Zero.
3. The Count operator is being used as a selection 
   criterion to require that only those patients whose 
   count of appearances in the immunisations table is zero.
4. We are _also_ requiring that the immunisations being
   counted contain the type "Rubella". The Inclusion
   operator in the other table pulls in all Patients
   where there just aren't any Immunisations at all.

Query

ANSWER: :PRIV:ANSWER.DB

PATIENTS.DB | PatientId   | Name  |
            | Check _pid! | Check |

PATIENTIMMUNISATIONS.DB | PatientId           |
                        | _pid, Count All = 0 | 

PATIENTIMMUNISATIONS.DB | ImmunisationType |
                        | Rubella          |

EndQuery

====================================
For more information on query by example, please see 
Chapter 19 of the book "Paradox Queries" by Dan Ehrmann.


Paradox Community Newsgroups


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.