Conditions on outer joins

Question

I'm trying to come up with an Access query which is equivalent to this oracle query

select ledef_name, 
       count(class.EVT_PK) timesTaught 
from ingenium.ledef course, 
     ingenium.evt class 
where course.LEDEF_PK = class.EVT_LEDEFFK(+)  
  and class.EVT_STARTDT(+) > to_date('2009-01-01', 'yyyy-mm-dd') 
group by ledef_name

In access I have

SELECT course.ledef_name, Count(class.EVT_PK) AS timesTaught
FROM INGENIUM_LEDEF AS course LEFT JOIN INGENIUM_EVT AS class ON course.LEDEF_PK = class.EVT_LEDEFFK
WHERE class.EVT_STARTDT>#1/1/2009#
GROUP BY course.ledef_name;

In the Oracle version I get rows with a 0 count but in Access those rows are missing. What is the access syntax to include rows where there is no match in class to a row in course?

Answer 1

Are you by any chance using floating point values in the join columns (dates are floating points in Access)? Floating point comparisons do not always match exactly because of rounding and representation issues (even thought they print out as exactly the same date in queries).

Answer 2

I think that you may need to add another condition to your WHERE clause. Add an " OR Is Null" to the WHERE clause and try that?

SELECT course.ledef_name, Count(class.EVT_PK) AS timesTaught
FROM INGENIUM_LEDEF AS course LEFT JOIN INGENIUM_EVT AS class ON course.LEDEF_PK = 
class.EVT_LEDEFFK
WHERE class.EVT_STARTDT>#1/1/2009# OR Is Null
GROUP BY course.ledef_name;