Help with Left Outer Join

[jmeckley]

I cannot for the life of me figure out why this query is not executing correctly.

Select
 b.Equipment_id,
 b.bill_month,
 b.bill_year,
 m.Equipment_id as id 
From
 bmonth b Left Outer Join Equipment_MeterRead m ON
  b.Equipment_id = m.Equipment_id AND
  b.bill_month = m.bill_month AND
  b.bill_year = m.bill_year 
Where
 m.MeterRead_id IS NULL

I want a list of equipment that does not have a meter read. To uniquely match records I need to link on equipment id, month and year.

If I remove the Where clause it will return all rows from bmonth and either the Equipment_MeterRead id or null if there is an existing record.

The minute I add the where clause it returns no rows!!!! I have been fighting with this all morning and cannot seem to make any progress.

any ideas are appreciated.

Jason Meckley
Web Application Developer
SSIC

 

[jmeckley]

ok my coworker figured this one out.
I need to use having instead of where... not sure why.

Jason Meckley
Web Application Developer
SSIC