Icon View Incident Report

Serious Serious
Reported By: Steve Forbes
Reported On: 10/26/1999
For: Version 1.18 Build 1
# 491 SQL Left Outer Joins Not Working Properly When First Record in Join Condition Causes NULLs

The following code creates two tables PRODUCT.DAT and USER.DAT. PRODUCT has a heirachical reference to a parent product via the column ParentId and to the product's human manager via the column ManagerId which references the USER table.

{ The following SQL statement which should return the result set:

Id  Description              ParentProduct           Manager
1   New Product                                      Steve Forbes
2   New Product Child        New Product             Phil Taylor
3   Another Child            New Product Child

}

SELECT
    Product.Description,
    Product_1.Description AS ParentProduct,
    User.KnownAs AS Manager
FROM (Product
    LEFT JOIN Product AS Product_1 ON Product.ParentId = Product_1.Id)
    LEFT JOIN User ON Product.ManagerId = User.Id
ORDER BY Product.Description



Comments Comments
The problem was exhibited when the first LEFT OUTER JOIN condition on the first record resulted in the right-hand side of the join producing a NULL record (no match).


Resolution Resolution
Fixed Problem on 10/27/1999 in version 1.19 build 1
Image