Not quite I sure I follow (sorry, almost out the door here), but I think
you just want your aggregate to an entry for every A regardless of B,
right? Try making your joins LEFT, and I think you'll have it:
...
FROM
tableA p LEFT OUTER JOIN
tableB e ON (p.use_no = e.use_no AND p.year = e.year and
e.duplicate_set is not null) LEFT OUTER JOIN
tableB d ON (e.duplicate_set = d.duplicate_set AND e.year =
d.year)
...
That should get your aggregate COUNT() to still return "1" where there is
only the one record in TableA, like 2009 3.
Cheers,
Jason
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319881
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
