I dont believe this is working with Access DB SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound
C S <[EMAIL PROTECTED]> wrote: >select *, exists ( >select * >from orders >where date between #date1# and #date2# >) as hasOrdered >from customers >order by name, id > >Use CFQUERYPARAM, of course. Another variation is a left join. Ditto on using cfqueryparam. SELECT c.ID, c.Name, c.Email, SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound FROM tblCustomers AS c LEFT JOIN tblOrders AS o ON c.ID = o.UserID AND o.Date BETWEEN @startDate AND @endDate GROUP BY c.ID, c.Name, c.Email ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304382 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
