Re: Query Help Please

2008-04-29 Thread C S 
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

FROMtblCustomers 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

Re: Query Help Please

2008-04-29 Thread Brian Sheridan 
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

Re: Query Help Please

2008-04-29 Thread C S 
I dont believe this is working with Access DB
  SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound

I think Access's version of CASE is IIF(..). Try using IIF instead and also try 
the query Barney suggested. One of them should work. 

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

Re: Query Help Please

2008-04-28 Thread Barney Boisvert 
select *, exists (
select *
from orders
where date between #date1# and #date2#
) as hasOrdered
from customers
order by name, id

Use CFQUERYPARAM, of course.

cheers,
barneyb

On Mon, Apr 28, 2008 at 8:57 PM, Brian Sheridan
[EMAIL PROTECTED] wrote:
 I will try to make this as simple as possible. Any help would be greatly 
 appreciated. I have 2 tables like below.

   tblCustomers
   ===
   ID | Name | Email |
   1, John, [EMAIL PROTECTED]
   2, Bob, [EMAIL PROTECTED]
   3, Steve, [EMAIL PROTECTED]
   ===


   tblOrders
   ===
   UserID | Date|
   1, 12/22/2007
   3, 1/2/2008

  ===


   I want to do a query like a scorecard view, that will list all CUSTOMERS 
 and show yes or no if they had an order bewtween 2 dates.



   RESULTS WOULD BE LIKE THIS IF I SEARCH BETWEEN 12/01/2007 - 1/30/2008
   -
 ID | Name | Email |
   1, John, YES
   2, Bob, NO
   3, Steve, YES



  

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