>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:304381
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
