SQL join looks odd, and does not appear to function correctly.

[jmmckee <jmmckee@xxxxxxxxxxxxxx>]

I was trying to create a count of items in INVENTORY_HEADER not referenced by PREFERENCE_DETAIL.  I used Crystal Report Writer to generate a starting point.

But, it looks odd.  I don't understand the oj in the FROM clause.  Or, for that matter, why INNER JOINN was used to connect INVENTORY_HEADER and PREFERENCE DETAIL.  As presented below, I do not see items that are unreferenced.  If I change INNER JOIN to OUTER JOIN, I definitely get the wrong results.  

Is this just how Microsoft SQL works, or is there some way to fix this?

John McKee


SELECT
    MAX(INVENTORY_HEADER."SYSTEM_ID"), 
    MAX(INVENTORY_HEADER."INVENTORY_ID"),
    MAX(PREFERENCE_DETAIL."RESOURCE_ID"), 
    count(preference_detail."RESOURCE_ID")
FROM
    { oj "ORSOSTEST"."ORSOSTEST"."INVENTORY_HEADER" INVENTORY_HEADER INNER JOIN "ORSOSTEST"."ORSOSTEST"."PREFERENCE_DETAIL" PREFERENCE_DETAIL ON
        INVENTORY_HEADER."SYSTEM_ID" = PREFERENCE_DETAIL."RESOURCE_ID"}
GROUP BY PREFERENCE_DETAIL."RESOURCE_ID"
ORDER BY
    count(preference_detail."RESOURCE_ID") ASC