left join on multiple fields for report

[tman135]

I have a report that needs to left join from one table on 4 different fields to another table and show counts of which SCIMS were used. The goal is to show all unique combinations of the 4 fields and then counts of which were used as well as zero counts of which were not.

This is the query which feeds the crystal report. The left join on 4 fields works fine until I put in the WHERE clause to evaluate the problem_type...then records stop dropping. Is there some sort of special syntax for the sql because of the 4 outer joins?

SELECT
    MODULE."SYSTEM_ID", MODULE."COMPONENT_ID", MODULE."ITEM_ID", MODULE."MODULE_ID",
    PROBLEMS."PROBLEM_ID"
FROM
    "ABC"."MODULE" MODULE LEFT JOIN "ABC"."PROBLEMS" PROBLEMS ON
        MODULE."SYSTEM_ID" = PROBLEMS."SYSTEM" AND
    MODULE."COMPONENT_ID" = PROBLEMS."COMPONENT" AND
    MODULE."ITEM_ID" = PROBLEMS."ITEM" AND
    MODULE."MODULE_ID" = PROBLEMS."MODULE"  
WHERE
    MODULE."SYSTEM_ID" LIKE 'ABC%' AND (PROBLEMS."PROBLEM_TYPE" = 'PROBLEM' OR PROBLEMS."PROBLEM_TYPE" IS NULL)

 

[mrees]

Your problems table is on the left join ok?

You are referencing this table in your where clause, thus effectively turning it into an Inner Join. (Basically you are saying something must exist in the right hand table, restricting those returned on the left side.)

If you were to do this in SQL, you would put the PROBLEMS."PROBLEM_TYPE" = 'PROBLEM' OR PROBLEMS."PROBLEM_TYPE" IS NULL in your join instead. I'm not sure if Crystal will allow this.

Hope this helps

MREES