Simple left join not working as expected
Posted on 2004-04-21
Hello All. I have a fairly simple LEFT JOIN in which I'm trying to get a list of counts and amounts grouped by a dimension. I want a list of every row of my dimension, with the corresponding count and amount (from two separate tables), listing 0 if there isn't anything. The following does not work properly:
a.seizure_disp_desc, count(Sub_Asset_Disp_Cd) AS Number, sum(Disp_amt) AS Amount
FROM seizure_disp_cd_dim a
LEFT JOIN t_sub_asset b ON seizure_disp_cd = sub_asset_disp_cd
LEFT JOIN t_disposition c ON b.sub_asset_id = c.sub_asset_id
WHERE Disp_Dt BETWEEN '1/1/01' AND '12/31/03'
GROUP BY a.seizure_disp_desc
However, if I remove the WHERE clause, it does work, giving my 0's where I expect it.
Can anyone explain to me what I am missing here and what I can do to make this work? Thanks!