>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)
Yes, I think that would work. There is probably a more elegant method, but you
could try joining back to the counts to get both totals.
SELECT m.county, m.rec_type, m.records, dup.records_with_dupes
FROM
(
SELECT county, rec_type, count(*) as records
FROM tableA
GROUP BY county, rec_type
)
m LEFT JOIN
(
SELECT p.county, p.rec_type, count(*) AS records_with_dupes
FROM tableA p
LEFT JOIN tableB e ON
(p.use_no = e.use_no AND p.year = e.year and e.dup_set
is not null)
LEFT JOIN tableB d ON
(e.dup_set = d.dup_set AND e.year = d.year)
GROUP BY p.county, p.rec_type
)
dup ON m.county = dup.county AND m.rec_type = dup.rec_type
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:319893
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
