Consultor Eletrônico

Status: Unverified

GOAL:

How to do a LEFT OUTER JOIN while restricting the selection from the table on the right

GOAL:

How to get all records from the table on the left when restricting the selection of records from the table on the right

FACT(s) (Environment):

Progress 9.1D

FIX:

Example:

Assuming records do exist in the price table where:
price."CUST-NUM" = '0100010'. And, price."part-num" = '0200020'

Also, there are no records in the invitem table where:
invitem."INV-DATE" >= '2003-01-01' for the specified left outer join.

The following query does not return any records because the where criteria does not find any records WHERE invitem."INV-DATE" >= '2003-01-01' - there is no result set where all 3 conditions are met.


SELECT price."CUST-NUM", price."PART-NUM"
FROM PRICE price
left outer join INVITEM invitem
ON price."PART-NUM"=invitem."PART-NUM"
AND price."CUST-NUM"=invitem."CUST-NUM"
WHERE (price."CUST-NUM" = '0100010'
and invitem."INV-DATE" >= '2003-01-01'
and price."part-num" = '0200020')


By comparison, the following query first looks at the invitem table and does the filtering based on the date condition before executing the LEFT OUTER JOIN with the price table. This query returns the records from the table on the left.

SELECT price."CUST-NUM",
price."PART-NUM"
FROM PRICE price
LEFT OUTER JOIN INVITEM invitem
ON ( price."PART-NUM" = invitem."PART-NUM"
AND price."CUST-NUM" = invitem."CUST-NUM"
AND invitem."INV-DATE" >= '2003-01-01'
)
WHERE ( price."CUST-NUM" = '0100010'
AND price."part-num" = '0200020'
)