![]() |
![]() |
|
![]() |
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. ![]() |
![]() |
|