Kbase P151995: How to select rows in a table which do NOT have a join
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
8/31/2009 |
|
Status: Unverified
GOAL:
How to define an absence of a join in a query
GOAL:
If OUTER-JOIN allows to select rows whether or not they have a join, how to select rows in a table which specifically do NOT have a specific join
GOAL:
How to define a negative join
GOAL:
When the field of my entity can have multiple values, how to select entities which do not have a specific field value?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
FIX:
In your query, use a dummy temporary table and use it to carry a "NOT AVAILABLE(<table name>)" condition, e.g.:
/* Create a dummy table a fill it in with one dummy element*/DEFINE TEMP-TABLE ttDummy FIELD nb AS INTEGER.
CREATE ttDummy.
FOR EACH customer,
FIRST product OUTER-JOIN WHERE <join to customer> AND product.name = 'OldProduct',
FIRST ttDummy WHERE NOT AVAILABLE(table2).
The above query looks for all customers who do NOT have a product called 'OldProduct'