Re: sql query help
- Original Message - From: "Jason Fisher" <[EMAIL PROTECTED]> > Cool, yeah I never remember until I do it when an aggregate query is going > to want HAVING vs WHERE. Glad it's working for you! This bites me too when I'm not paying attention. Just remember that the WHERE applies to the records BEFORE the are grouped up and the HAVING applies to the grouped result set after the aggregates have been applied. ~Brad ~| 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:316384 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
Cool, yeah I never remember until I do it when an aggregate query is going to want HAVING vs WHERE. Glad it's working for you! ~| 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:316379 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
NM, got it... changed the where clause to having and moved it below the group by... seems to be working so far! Thanks! ~| 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:316373 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
didn't work, got an error. changed the isnull to ifnull, got a "invalid use of a group function" error... I don't even know how to fix that...=( > Try this, I think it's what you're looking for: > > SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, p. > short_description, p.quantity, p.image > FROM tblproducts as p LEFT JOIN > tblorder_list as o ON p.sku = o.sku #can_afford# > WHERE SUM(ISNULL(o.qty, 0)) < p.quantity > GROUP BY p.sku ~| 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:316372 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
> In MS SQL Server it's ISNULL(), but can't speak for other platforms. > Can't recall what it is in Oracle, might just be NULL(). IIRC in Oracle it is NVL. There is also COALESCE, which is usually a safe bet with most databases. ~| 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:316349 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql query help
In MS SQL Server it's ISNULL(), but can't speak for other platforms. Can't recall what it is in Oracle, might just be NULL(). ~| 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:316319 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
i believe the correct function to use is IFNULL(), not ISNULL()... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Jason Fisher wrote: > Try this, I think it's what you're looking for: > > SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, > p.short_description, p.quantity, p.image > FROM tblproducts as p LEFT JOIN > tblorder_list as o ON p.sku = o.sku #can_afford# > WHERE SUM(ISNULL(o.qty, 0)) < p.quantity > GROUP BY p.sku > > > ~| 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:316311 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql query help
Try this, I think it's what you're looking for: SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, p.short_description, p.quantity, p.image FROM tblproducts as p LEFT JOIN tblorder_list as o ON p.sku = o.sku #can_afford# WHERE SUM(ISNULL(o.qty, 0)) < p.quantity GROUP BY p.sku ~| 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:316300 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Query Help
thanks to everyone who replied, problem solved. Jenny -Original Message- From: Rafael Marquez [mailto:[EMAIL PROTECTED] Sent: 04 April 2007 21:06 To: CF-Talk Subject: RE: SQL Query Help Well, it's not a brain fart. That query is kinda kinky. Use this as an example, replacing your tables and fields, it should work BASED ON DATE! SELECT DISTINCT T.CustomerID, T.TransactionDate, T.ID AS InvoiceID FROM dbo.Transactions T INNER JOIN (SELECT MAX(TransactionDate) AS maxdate, CustomerID FROM Transactions WHERE deleted = 0 GROUP BY CustomerID) maxresults ON T.CustomerID = maxresults.CustomerID AND T.TransactionDate = maxresults.maxdate WHERE (T.Deleted = 0) AND (T.Status <> 'Pending') AND (T.AccountType = 'Customer') GROUP BY T.CustomerID, T.TransactionDate, T.ID Hope this helps. -Original Message- From: Gaulin, Mark [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 3:39 PM To: CF-Talk Subject: RE: SQL Query Help Need more info: What would the query be to return the last invoice for a single customer? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 8:58 AM To: CF-Talk Subject: SQL Query Help having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274660 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Query Help
Well, it's not a brain fart. That query is kinda kinky. Use this as an example, replacing your tables and fields, it should work BASED ON DATE! SELECT DISTINCT T.CustomerID, T.TransactionDate, T.ID AS InvoiceID FROM dbo.Transactions T INNER JOIN (SELECT MAX(TransactionDate) AS maxdate, CustomerID FROM Transactions WHERE deleted = 0 GROUP BY CustomerID) maxresults ON T.CustomerID = maxresults.CustomerID AND T.TransactionDate = maxresults.maxdate WHERE (T.Deleted = 0) AND (T.Status <> 'Pending') AND (T.AccountType = 'Customer') GROUP BY T.CustomerID, T.TransactionDate, T.ID Hope this helps. -Original Message- From: Gaulin, Mark [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 3:39 PM To: CF-Talk Subject: RE: SQL Query Help Need more info: What would the query be to return the last invoice for a single customer? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 8:58 AM To: CF-Talk Subject: SQL Query Help having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274523 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Query Help
Need more info: What would the query be to return the last invoice for a single customer? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 8:58 AM To: CF-Talk Subject: SQL Query Help having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274519 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Query Help
Assuming the invoice numbers increase each time, you can use MAX() and GROUP BY to get what you need. SELECT customer, MAX(invoiceNum) AS lastNum FROM table GROUP BY customer If invoices are not numbered sequentially, hopefully you have a date field that you can add to the grouping. M!ke -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 7:58 AM To: CF-Talk Subject: SQL Query Help having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274520 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Query Help
Maybe something like this (which I cannot test): SELECT c.id, c.name, i.* FROM customer c LEFT OUTER JOIN ( -- Get max (most recent) id (assuming pkey) of -- each invoice as grouped by client. SELECT i2.customer_id MAX( i2.id ) AS invoice_id FROM invoice i2 GROUP BY i2.customer_id ) AS inv ON c.id = inv.customer_Id LEFT OUTER JOIN invoice i ON inv.invoice_id = i.id We create an interim table that has JUST the customer id and MAX invoice Id. Then we join that to customers, and then join that to invoices (only were the max id = the invoice id). .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 8:58 AM To: CF-Talk Subject: SQL Query Help having brain fart here ... ughh scenario: customer table, invoice table I'd like to do a query to list the last invoice for each customer. Can someone point me in the right direction please? TIA, Jenny ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274517 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
This is how I'm doing it for now... select count(*) as dt , left(datetime,11) as datetime from sitelog where datepart(year,datetime) = '#theyear#' and datepart(month,datetime) = '#themonth#' and datepart(year,datetime) = '#listFirst(url.datestat,"/")#' and datepart(month,datetime) = '#listLast(url.datestat,"/")#' AND userID <> 0 AND userID = #url.u# AND groupID = #url.g# AND ID = #url.id# AND script_name = '#url.script_name#' group by left(datetime,11) order by left(datetime,11) desc Michael At 09:50 PM 2/5/2007, you wrote: >Mik Muller wrote: >> >> select left(datetime,11), count(*) as dt >> from sitelog >> where datetime >= '2007-02-03 00:00' >> group by left(datetime,11) >> order by left(datetime,11) desc >> >> >> Well, now that I look at it, the order by is ordering by "jan 1 2006" etc in >> desc alphabetical order. How do I get this field to be sorted by -mm-dd? >> Do I have to go through all this... >> >> http://www.databasejournal.com/features/mssql/article.php/10894_2197931_2 >> > >Read up on the CONVERT formats in books online... > >select CONVERT(char(8),datetime,112), count(*) as dt >from sitelog >where datetime >= '2007-02-03 00:00' >group by CONVERT(char(8),datetime,112) >order by CONVERT(char(8),datetime,112) desc > >Or use the Day, Month and Year functions. > >When you use the LEFT function as you did above, it is actually doing a >CONVERT...just to the default format. > > > ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268774 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql query help
Mik Muller wrote: > > select left(datetime,11), count(*) as dt > from sitelog > where datetime >= '2007-02-03 00:00' > group by left(datetime,11) > order by left(datetime,11) desc > > > Well, now that I look at it, the order by is ordering by "jan 1 2006" etc in > desc alphabetical order. How do I get this field to be sorted by -mm-dd? > Do I have to go through all this... > > http://www.databasejournal.com/features/mssql/article.php/10894_2197931_2 > Read up on the CONVERT formats in books online... select CONVERT(char(8),datetime,112), count(*) as dt from sitelog where datetime >= '2007-02-03 00:00' group by CONVERT(char(8),datetime,112) order by CONVERT(char(8),datetime,112) desc Or use the Day, Month and Year functions. When you use the LEFT function as you did above, it is actually doing a CONVERT...just to the default format. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268773 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL query help needed
Elena Aminova wrote: > > id DateQty Dollars > 1605/30/2003 4 890.00 > 2707/28/2004 1 300.00 > 2901/17/2003 4 108.00 > 5501/21/2002 1 105.00 > 5609/20/2003 7 700.00 > 5609/16/2003 0 0.00 > 5609/10/2003 39 1,735.00 > 5603/16/2001 2 1,118.00 > 5603/05/2001 1 450.00 > 5603/05/2001 13 2,270.00 > 123 12/29/2001 1 70.00 > 175 08/28/2003 3 567.00 > 175 07/16/2003 2 192.00 > > I need to display one line for each cust_num, with the latest date for that > cust_num, and the sum of each the qty and dollars. SELECT id, MAX(Date), SUM(Qty), Sum(Dollars) FROM table GROUP BY id Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:209694 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Help Please.
> 2.) Can someone please suggest a good book on SQL syntax "Teach Yourself SQL in 10 Minutes" by Ben Forta. ISBN 0-672-32128-9 ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Thanks Umer & Others, You guys have been of great help. Will try the query today.. Regards, Mark - Original Message - From: "Umer Farooq" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, October 11, 2004 8:17 PM Subject: Re: SQL Query Help Please. > Here you.. go.. returns.. orderId, date, customerId, customer name, > paymentReceived, amount due (per order)... , > > > SELECT orders.orderId, order.orderDate, orders.customerID, >customers.customerName, customerPayment.PaymentReceived >(SELECT sum(unitPrice * quantity) > FROM orderDetails > WHERE orderDetails.orderID = orders.orderID ) AS amountDue, > > FROM (orders LEFT JOIN customerPayment > ON customerPayment.orderID = orders.orderID) >LEFT JOIN customer > ON customer.customerID = orders.customerID > > Nomad wrote: > > > > > The Database tables and fields are: > > > > Orders (orderid,customerid,Orderdate) > > > > OrderDetails(Orderid,UnitPrice,productid,quantity) > > > > Customer(Customerid, customername, address) > > > > CustomerPayments(orderid,PaymentReceived) > > > > > > > > My purpose is to track the account status of the customer for each > > order > > > > they have placed. > > > > > > > > The desired format of result obtained from the query is like this: > > > > > > > > Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a > > > > calculated column). > > > -- > Umer Farooq > Octadyne Systems > [EMAIL PROTECTED] > +1 (519) 772-5424 voice > +1 (519) 635-2795 mobile > +1 (208) 275-3824 fax > > > LOOKING FOR A USED CAR IN IOWA VISIT: http://www.IowaMotors.com > > > WARNING: --- The information contained in > this document and attachments is confidential and intended only for the > person(s) named above. If you are not the intended recipient you are > hereby notified that any disclosure, copying, distribution, or any other > use of the information is strictly prohibited. If you have received > this document by mistake, please notify the sender immediately and > destroy this document and attachments without making any copy of any kind. > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query Help Please.
2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Other beginner resources: 1) http://www.sqlcourse.com/ 2) http://sqlcourse2.com/ 3) http://www.freeprogrammingresources.com/sql.html (links to many others) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Here you.. go.. returns.. orderId, date, customerId, customer name, paymentReceived, amount due (per order)... , SELECT orders.orderId, order.orderDate, orders.customerID, customers.customerName, customerPayment.PaymentReceived (SELECT sum(unitPrice * quantity) FROM orderDetails WHERE orderDetails.orderID = orders.orderID ) AS amountDue, FROM (orders LEFT JOIN customerPayment ON customerPayment.orderID = orders.orderID) LEFT JOIN customer ON customer.customerID = orders.customerID Nomad wrote: > > > The Database tables and fields are: > > > Orders (orderid,customerid,Orderdate) > > > OrderDetails(Orderid,UnitPrice,productid,quantity) > > > Customer(Customerid, customername, address) > > > CustomerPayments(orderid,PaymentReceived) > > > > > > My purpose is to track the account status of the customer for each > order > > > they have placed. > > > > > > The desired format of result obtained from the query is like this: > > > > > > Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a > > > calculated column). -- Umer Farooq Octadyne Systems [EMAIL PROTECTED] +1 (519) 772-5424 voice +1 (519) 635-2795 mobile +1 (208) 275-3824 fax LOOKING FOR A USED CAR IN IOWA VISIT: http://www.IowaMotors.com WARNING: --- The information contained in this document and attachments is confidential and intended only for the person(s) named above. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution, or any other use of the information is strictly prohibited. If you have received this document by mistake, please notify the sender immediately and destroy this document and attachments without making any copy of any kind. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Hi, Here you.. go.. returns.. orderId, date, customerId, customer name, paymentReceived, amount due per order... , SELECT orders.orderId, order.orderDate, orders.customerID, customers.customerName, customerPayment.PaymentReceived (SELECT sum(unitPrice * quantity) FROM orderDetails WHERE orderDetails.orderID = orders.orderID ) AS amountDue, FROM (orders LEFT JOIN customerPayment ON customerPayment.orderID = orders.orderID) LEFT JOIN customer ON customer.customerID = orders.customerID Nomad wrote: > Hi Josh, > Thanks for your reply. > Your query is similar to what I have been trying. > > My query is: > > SELECT > customer.Customercompanyname, > orders.Orderid, > orders.Orderdate, > (Orderdetails.unitprice * Orderdetails.quantity) As OrderValue, > CustomerPayments.PRIG As PaymentsReceived > > FROM Orders , OrderDetails, Customer, CustomerPayments > WHERE 0=0 > AND Orderdetails.orderid=Orders.orderid > AND CustomerPayments.orderid=Orders.orderid > AND Orders.customerid=Customer.customerid > > The result is something like this which is wrong: > Notice the repetition of the OrderId field. Ideally there should be one row > for each order. > > Customer Name OrderID, OrderDate, OrderValue Payments > Received. > > Company 1 1 11/10/2004 55 > 500 > Company 1 1 11/10/2004 31898 > 500 > Company 1 1 11/10/20045678 > 500 > Company5 4 11/10/20042320 > 400 > Company5 4 11/10/20044740 > 400 > Company5 4 11/10/2004 26400 > 400 > Company5 4 11/10/2004544600 > 400 > - Original Message - > From: "Josh" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Monday, October 11, 2004 6:07 PM > Subject: Re: SQL Query Help Please. > > > Here's a start, though you'll have to explain your tables and your > > calculations further. > > > > SELECT > > c.Customername, > > a.Orderid, > > a.Orderdate, > > ( where is this being stored? AmountReceived maybe this could be > > d.PaymentReceived AS AmountReceived ? ), > > ( put your formula for calculating AmountDue here, possibly a subquery ) > > FROM Orders a, OrderDetails b, Customer c, CustomerPayments d > > WHERE a.orderid = b.orderid > > AND a.orderid = d.orderid > > AND c.customerid = a.customerid > > > > Hope that helps some... > > > > I would recomend the SQL book by forta, or just look online for a good > > tutorial. > > > > -Josh > > > > -- > > Exciteworks, Inc > > Expert Hosting for less! > > *Ask for a free 30 day trial!* > > http://exciteworks.com > > > > Plans starting at -$12.95- including MS SQL Server! > > > > > > > > > > Nomad wrote: > > > > > Hello! > > > > > > I am trying to create a join of four tables to get data from a db > in the > > > format I want. > > > > > > The Database tables and fields are: > > > Orders (orderid,customerid,Orderdate) > > > OrderDetails(Orderid,UnitPrice,productid,quantity) > > > Customer(Customerid, customername, address) > > > CustomerPayments(orderid,PaymentReceived) > > > > > > My purpose is to track the account status of the customer for each > order > > > they have placed. > > > > > > The desired format of result obtained from the query is like this: > > > > > > Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a > > > calculated column). > > > > > > I am using Access 2000 with CFusion. > > > > > > 1.)Can someone help me with the SQL syntax please. I have tried > several > > > permutations and combinations of sql (to the best of my knowledge) but > > > without success. > > > 2.) Can someone please suggest a good book on SQL syntax that will > clear > > > thing up for me. > > > > > > Many Thanks in Advance, > > > > > > Mark Taylor > > > Admin > > > > > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Hi Josh, Thanks for your reply. Your query is similar to what I have been trying. My query is: SELECT customer.Customercompanyname, orders.Orderid, orders.Orderdate, (Orderdetails.unitprice * Orderdetails.quantity) As OrderValue, CustomerPayments.PRIG As PaymentsReceived FROM Orders , OrderDetails, Customer, CustomerPayments WHERE 0=0 AND Orderdetails.orderid=Orders.orderid AND CustomerPayments.orderid=Orders.orderid AND Orders.customerid=Customer.customerid The result is something like this which is wrong: Notice the repetition of the OrderId field. Ideally there should be one row for each order. Customer Name OrderID, OrderDate, OrderValue Payments Received. Company 1 1 11/10/2004 55 500 Company 1 1 11/10/2004 31898 500 Company 1 1 11/10/20045678 500 Company5 4 11/10/20042320 400 Company5 4 11/10/20044740 400 Company5 4 11/10/2004 26400 400 Company5 4 11/10/2004544600 400 - Original Message - From: "Josh" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, October 11, 2004 6:07 PM Subject: Re: SQL Query Help Please. > Here's a start, though you'll have to explain your tables and your > calculations further. > > SELECT > c.Customername, > a.Orderid, > a.Orderdate, > ( where is this being stored? AmountReceived maybe this could be > d.PaymentReceived AS AmountReceived ? ), > ( put your formula for calculating AmountDue here, possibly a subquery ) > FROM Orders a, OrderDetails b, Customer c, CustomerPayments d > WHERE a.orderid = b.orderid > AND a.orderid = d.orderid > AND c.customerid = a.customerid > > Hope that helps some... > > I would recomend the SQL book by forta, or just look online for a good > tutorial. > > -Josh > > -- > Exciteworks, Inc > Expert Hosting for less! > *Ask for a free 30 day trial!* > http://exciteworks.com > > Plans starting at -$12.95- including MS SQL Server! > > > > > Nomad wrote: > > > Hello! > > > > I am trying to create a join of four tables to get data from a db in the > > format I want. > > > > The Database tables and fields are: > > Orders (orderid,customerid,Orderdate) > > OrderDetails(Orderid,UnitPrice,productid,quantity) > > Customer(Customerid, customername, address) > > CustomerPayments(orderid,PaymentReceived) > > > > My purpose is to track the account status of the customer for each order > > they have placed. > > > > The desired format of result obtained from the query is like this: > > > > Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a > > calculated column). > > > > I am using Access 2000 with CFusion. > > > > 1.)Can someone help me with the SQL syntax please. I have tried several > > permutations and combinations of sql (to the best of my knowledge) but > > without success. > > 2.) Can someone please suggest a good book on SQL syntax that will clear > > thing up for me. > > > > Many Thanks in Advance, > > > > Mark Taylor > > Admin > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Here's a start, though you'll have to explain your tables and your calculations further. SELECT c.Customername, a.Orderid, a.Orderdate, ( where is this being stored? AmountReceived maybe this could be d.PaymentReceived AS AmountReceived ? ), ( put your formula for calculating AmountDue here, possibly a subquery ) FROM Orders a, OrderDetails b, Customer c, CustomerPayments d WHERE a.orderid = b.orderid AND a.orderid = d.orderid AND c.customerid = a.customerid Hope that helps some... I would recomend the SQL book by forta, or just look online for a good tutorial. -Josh -- Exciteworks, Inc Expert Hosting for less! *Ask for a free 30 day trial!* http://exciteworks.com Plans starting at -$12.95- including MS SQL Server! Nomad wrote: > Hello! > > I am trying to create a join of four tables to get data from a db in the > format I want. > > The Database tables and fields are: > Orders (orderid,customerid,Orderdate) > OrderDetails(Orderid,UnitPrice,productid,quantity) > Customer(Customerid, customername, address) > CustomerPayments(orderid,PaymentReceived) > > My purpose is to track the account status of the customer for each order > they have placed. > > The desired format of result obtained from the query is like this: > > Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a > calculated column). > > I am using Access 2000 with CFusion. > > 1.)Can someone help me with the SQL syntax please. I have tried several > permutations and combinations of sql (to the best of my knowledge) but > without success. > 2.) Can someone please suggest a good book on SQL syntax that will clear > thing up for me. > > Many Thanks in Advance, > > Mark Taylor > Admin > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Hi, http://www.techonthenet.com/access/queries/joins1.htm For beginner I would suggest.. SAMS SQL in 21 Days.. and Google.. :-) Nomad wrote: > Hello! > > I am trying to create a join of four tables to get data from a db in the > format I want. > > The Database tables and fields are: > Orders (orderid,customerid,Orderdate) > OrderDetails(Orderid,UnitPrice,productid,quantity) > Customer(Customerid, customername, address) > CustomerPayments(orderid,PaymentReceived) > > My purpose is to track the account status of the customer for each order > they have placed. > > The desired format of result obtained from the query is like this: > > Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a > calculated column). > > I am using Access 2000 with CFusion. > > 1.)Can someone help me with the SQL syntax please. I have tried several > permutations and combinations of sql (to the best of my knowledge) but > without success. > 2.) Can someone please suggest a good book on SQL syntax that will clear > thing up for me. > > Many Thanks in Advance, > > Mark Taylor > Admin > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query Help (I'm stuck again)
Ok, hopefully this is my last question regarding this After doing some testing, the query seems to pull the data I am looking for. The next thing I want to do is, make this query change the status of its results from ACTIVE to CLOSED. Using the statement below, it is closing all of the records that are ACTIVE and have an expired end date. It does not seem to even care about the bid amounts. UPDATE auction_items SET Status = 'CLOSED' WHERE EXISTS( SELECT ai.Product_ID, ai.Status, ai.Reserve_Price FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate < #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status, ai.Reserve_Price HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) ); This statment does seem to pull the correct data. Original Message: >From: "Pascal Peters" <[EMAIL PROTECTED]> >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: SQL Query Help (I'm stuck again) >Date: Wed, 10 Sep 2003 16:51:14 +0200 >You need ai.Reserve_Price in the select (NOT the aggregate function) AND the HAVING >clause. Because of the inner join, you will NOT get the records that did not receive >a bid. > > -Oorspronkelijk bericht- > Van: grovem [mailto:[EMAIL PROTECTED] > Verzonden: wo 10/09/2003 16:43 > Aan: CF-Talk > CC: > Onderwerp: RE: SQL Query Help (I'm stuck again) > > > > If I ad the Max(ab.Bid_Amount) to the select statment and remove the HAVING > line, I do not get any errors, but I also return all records that have had a bid. > > When I ad the HAVING line [below] I receve the error > > ODBC Error Code = 37000 (Syntax error or access violation) > [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that > does not include the specified expression 'MAX(ab.Bid_Amount)>ai.Reserve_Price' as > part of an aggregate function. > > >dbtype="ODBC"> > SELECT ai.Product_ID, ai.Status, > MAX(ab.Bid_Amount) AS HighBid > FROM Auction_Items ai, Auction_Bids ab > WHERE ai.Status = 'ACTIVE' > AND ai.date_EndDate < #createODBCDate(now())# > AND ai.Product_ID = ab.Product_ID > GROUP BY ai.Product_ID, ai.Status > HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) > > > > basically this needs to pull all of the records that did not receive a bid > meeting or exceeding the reserve price. > > > > > ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: SQL Query Help (I'm stuck again)
that worked, thank you very much. So far out of all the resources I have used in the past, this one is by far the best. Original Message: >From: "Pascal Peters" <[EMAIL PROTECTED]> >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: SQL Query Help (I'm stuck again) >Date: Wed, 10 Sep 2003 16:51:14 +0200 >You need ai.Reserve_Price in the select (NOT the aggregate function) AND the HAVING >clause. Because of the inner join, you will NOT get the records that did not receive >a bid. > > -Oorspronkelijk bericht- > Van: grovem [mailto:[EMAIL PROTECTED] > Verzonden: wo 10/09/2003 16:43 > Aan: CF-Talk > CC: > Onderwerp: RE: SQL Query Help (I'm stuck again) > > > > If I ad the Max(ab.Bid_Amount) to the select statment and remove the HAVING > line, I do not get any errors, but I also return all records that have had a bid. > > When I ad the HAVING line [below] I receve the error > > ODBC Error Code = 37000 (Syntax error or access violation) > [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that > does not include the specified expression 'MAX(ab.Bid_Amount)>ai.Reserve_Price' as > part of an aggregate function. > > >dbtype="ODBC"> > SELECT ai.Product_ID, ai.Status, > MAX(ab.Bid_Amount) AS HighBid > FROM Auction_Items ai, Auction_Bids ab > WHERE ai.Status = 'ACTIVE' > AND ai.date_EndDate < #createODBCDate(now())# > AND ai.Product_ID = ab.Product_ID > GROUP BY ai.Product_ID, ai.Status > HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) > > > > basically this needs to pull all of the records that did not receive a bid > meeting or exceeding the reserve price. > > > > > ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
RE: SQL Query Help (I'm stuck again)
You need ai.Reserve_Price in the select (NOT the aggregate function) AND the HAVING clause. Because of the inner join, you will NOT get the records that did not receive a bid. -Oorspronkelijk bericht- Van: grovem [mailto:[EMAIL PROTECTED] Verzonden: wo 10/09/2003 16:43 Aan: CF-Talk CC: Onderwerp: RE: SQL Query Help (I'm stuck again) If I ad the Max(ab.Bid_Amount) to the select statment and remove the HAVING line, I do not get any errors, but I also return all records that have had a bid. When I ad the HAVING line [below] I receve the error ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'MAX(ab.Bid_Amount)>ai.Reserve_Price' as part of an aggregate function. SELECT ai.Product_ID, ai.Status, MAX(ab.Bid_Amount) AS HighBid FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate < #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) basically this needs to pull all of the records that did not receive a bid meeting or exceeding the reserve price. ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: SQL Query Help (I'm stuck again)
If I ad the Max(ab.Bid_Amount) to the select statment and remove the HAVING line, I do not get any errors, but I also return all records that have had a bid. When I ad the HAVING line [below] I receve the error ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'MAX(ab.Bid_Amount)>ai.Reserve_Price' as part of an aggregate function. SELECT ai.Product_ID, ai.Status, MAX(ab.Bid_Amount) AS HighBid FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate < #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) basically this needs to pull all of the records that did not receive a bid meeting or exceeding the reserve price. Thanks Again Original Message: >From: "Michael Traher" <[EMAIL PROTECTED]> >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: SQL Query Help (I'm stuck again) >Date: Wed, 10 Sep 2003 15:28:19 +0100 >You still need the 'MAX(ab.Bid_Amount)' in the SELECT part of the query. > >The HAVING clause does a secondary filter on this > >Michael Traher >Systems Manager >ICLP (London) >Tel: UK +44 (0) 20 8256 9072 >Fax: UK +44 (0) 20 8681 0234 > >This e-mail may contain privileged and confidential information and/or >copyright material and is intended for the use of the addressee only. If >you receive this e-mail by mistake please advise the sender immediately >by using the reply facility in your e-mail software and delete this >e-mail from your computer system. You may not deliver, copy or disclose >its contents to anyone else. Any unauthorised use may be unlawful. Any >views expressed in this e-mail are those of the individual sender and >may not necessarily reflect the views of ICLP. > > > >-Original Message- >From: grovem [mailto:[EMAIL PROTECTED] >Sent: 10 September 2003 15:16 >To: CF-Talk >Subject: RE: SQL Query Help (I'm stuck again) > >Using the following code, I get > >dbtype="ODBC"> >SELECT ai.Product_ID, ai.Status >FROM Auction_Items ai, Auction_Bids ab >WHERE ai.Status = 'ACTIVE' >AND ai.date_EndDate < #createODBCDate(now())# >AND ai.Product_ID = ab.Product_ID >GROUP BY ai.Product_ID, ai.Status >HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) > > >Non-Sale Report > >#Product_ID# #status# > > >ERROR >--- >ODBC Error Code = 37000 (Syntax error or access violation) > >[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query >that does not include the specified expression >'ai.Reserve_Price>MAX(ab.Bid_Amount)' as part of an aggregate function. > >I think we are close, but still having problems. > >Original Message: >>From: "Pascal Peters" <[EMAIL PROTECTED]> >>To: CF-Talk <[EMAIL PROTECTED]> >>Subject: RE: SQL Query Help (I'm stuck again) >>Date: Wed, 10 Sep 2003 08:36:33 +0200 > >>this should work >> >>SELECT ai.Product_ID, ai.Status >>FROM Auction_Items ai, Auction_Bids ab >>WHERE ai.Status = 'ACTIVE' >>AND ai.date_EndDate < #createODBCDate(now())# >>AND ai.Product_ID = ab.Product_ID >>GROUP BY ai.Product_ID, ai.Status >>HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) >> >> -Oorspronkelijk bericht- >> Van: Michael C.Grove [mailto:[EMAIL PROTECTED] >> Verzonden: wo 10/09/2003 7:26 >> Aan: CF-Talk >> CC: >> Onderwerp: SQL Query Help (I'm stuck again) >> >> >> >> SAMPLE 2 >> UPDATE Auction_Items >> SET Status = 'CLOSED' >> WHERE EXISTS(SELECT ai.Product_ID, ai.Status >> FROM Auction_Items ai, Auction_Bids ab >> WHERE ai.Status = 'ACTIVE' >> AND ai.date_EndDate < #createODBCDate(now())# >> AND ai.Product_ID = ab.Product_ID >> AND ai.Reserve_Price > ab.Bid_Amount >> GROUP BY ai.Product_ID, ai.Status); >> >> >> >> > > ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
RE: SQL Query Help (I'm stuck again)
You still need the 'MAX(ab.Bid_Amount)' in the SELECT part of the query. The HAVING clause does a secondary filter on this Michael Traher Systems Manager ICLP (London) Tel: UK +44 (0) 20 8256 9072 Fax: UK +44 (0) 20 8681 0234 This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of ICLP. -Original Message- From: grovem [mailto:[EMAIL PROTECTED] Sent: 10 September 2003 15:16 To: CF-Talk Subject: RE: SQL Query Help (I'm stuck again) Using the following code, I get SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate < #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) Non-Sale Report #Product_ID# #status# ERROR --- ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'ai.Reserve_Price>MAX(ab.Bid_Amount)' as part of an aggregate function. I think we are close, but still having problems. Original Message: >From: "Pascal Peters" <[EMAIL PROTECTED]> >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: SQL Query Help (I'm stuck again) >Date: Wed, 10 Sep 2003 08:36:33 +0200 >this should work > >SELECT ai.Product_ID, ai.Status >FROM Auction_Items ai, Auction_Bids ab >WHERE ai.Status = 'ACTIVE' >AND ai.date_EndDate < #createODBCDate(now())# >AND ai.Product_ID = ab.Product_ID >GROUP BY ai.Product_ID, ai.Status >HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) > > -Oorspronkelijk bericht- > Van: Michael C.Grove [mailto:[EMAIL PROTECTED] > Verzonden: wo 10/09/2003 7:26 > Aan: CF-Talk > CC: > Onderwerp: SQL Query Help (I'm stuck again) > > > > SAMPLE 2 > UPDATE Auction_Items > SET Status = 'CLOSED' > WHERE EXISTS(SELECT ai.Product_ID, ai.Status > FROM Auction_Items ai, Auction_Bids ab > WHERE ai.Status = 'ACTIVE' > AND ai.date_EndDate < #createODBCDate(now())# > AND ai.Product_ID = ab.Product_ID > AND ai.Reserve_Price > ab.Bid_Amount > GROUP BY ai.Product_ID, ai.Status); > > > > ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: SQL Query Help (I'm stuck again)
Using the following code, I get SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate < #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) Non-Sale Report #Product_ID# #status# ERROR --- ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'ai.Reserve_Price>MAX(ab.Bid_Amount)' as part of an aggregate function. I think we are close, but still having problems. Original Message: >From: "Pascal Peters" <[EMAIL PROTECTED]> >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: SQL Query Help (I'm stuck again) >Date: Wed, 10 Sep 2003 08:36:33 +0200 >this should work > >SELECT ai.Product_ID, ai.Status >FROM Auction_Items ai, Auction_Bids ab >WHERE ai.Status = 'ACTIVE' >AND ai.date_EndDate < #createODBCDate(now())# >AND ai.Product_ID = ab.Product_ID >GROUP BY ai.Product_ID, ai.Status >HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) > > -Oorspronkelijk bericht- > Van: Michael C.Grove [mailto:[EMAIL PROTECTED] > Verzonden: wo 10/09/2003 7:26 > Aan: CF-Talk > CC: > Onderwerp: SQL Query Help (I'm stuck again) > > > > SAMPLE 2 > UPDATE Auction_Items > SET Status = 'CLOSED' > WHERE EXISTS(SELECT ai.Product_ID, ai.Status > FROM Auction_Items ai, Auction_Bids ab > WHERE ai.Status = 'ACTIVE' > AND ai.date_EndDate < #createODBCDate(now())# > AND ai.Product_ID = ab.Product_ID > AND ai.Reserve_Price > ab.Bid_Amount > GROUP BY ai.Product_ID, ai.Status); > > > > ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: SQL Query Help (I'm stuck again)
this should work SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate < #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID GROUP BY ai.Product_ID, ai.Status HAVING ai.Reserve_Price > MAX(ab.Bid_Amount) -Oorspronkelijk bericht- Van: Michael C.Grove [mailto:[EMAIL PROTECTED] Verzonden: wo 10/09/2003 7:26 Aan: CF-Talk CC: Onderwerp: SQL Query Help (I'm stuck again) SAMPLE 2 UPDATE Auction_Items SET Status = 'CLOSED' WHERE EXISTS(SELECT ai.Product_ID, ai.Status FROM Auction_Items ai, Auction_Bids ab WHERE ai.Status = 'ACTIVE' AND ai.date_EndDate < #createODBCDate(now())# AND ai.Product_ID = ab.Product_ID AND ai.Reserve_Price > ab.Bid_Amount GROUP BY ai.Product_ID, ai.Status); ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: SQL query help
That was a "bit" short !! [sic] E.g. select * Where cast(column as bit) = 1 Wg > -Original Message- > From: webguy [mailto:[EMAIL PROTECTED]] > Sent: 30 January 2003 16:14 > To: CF-Talk > Subject: RE: SQL query help > > > Use the cast function > > WG > > > -Original Message- > > From: Jeff Beer [mailto:[EMAIL PROTECTED]] > > Sent: 30 January 2003 16:00 > > To: CF-Talk > > Subject: OT: SQL query help > > > > > > Using SQL Server, how would I define a query to return ranges of ID's > > (primary key) where a tinyint field was not true? > > > > I'm looking for any ranges (eg; 12202 - 12249 and 14477 - 15225) > > where that > > tinyint field is 0. I'd like to specify a minimum number of > > records in that > > range, but it's not absolutely necessary. Something like: > > > > show me sequential ranges > > where myTinyint = 0 > > and total records in each range > 100 > > > > Any help is greatly appreciated! > > > > > > > > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL query help
Use the cast function WG > -Original Message- > From: Jeff Beer [mailto:[EMAIL PROTECTED]] > Sent: 30 January 2003 16:00 > To: CF-Talk > Subject: OT: SQL query help > > > Using SQL Server, how would I define a query to return ranges of ID's > (primary key) where a tinyint field was not true? > > I'm looking for any ranges (eg; 12202 - 12249 and 14477 - 15225) > where that > tinyint field is 0. I'd like to specify a minimum number of > records in that > range, but it's not absolutely necessary. Something like: > > show me sequential ranges > where myTinyint = 0 > and total records in each range > 100 > > Any help is greatly appreciated! > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Query Help - Access Lookup tables and query output not accurate - not working
I don't fully undertand what you're doing. The first two queries, are those examples of the data inside those tables, or are they views/queries of some other table. It seems like all you need is a simple table join. Perhaps you could send me the database and cfml off list and I could look at it for you. You'll have to email me at [EMAIL PROTECTED] Fregas - Original Message - From: "Jason Miller" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, January 10, 2003 7:03 PM Subject: SQL Query Help - Access Lookup tables and query output not accurate - not working > Hello List - > I can not get this query down for the life of me. After a wasted > afternoon - I am hoping ( I know you can) someone will assist me. > > I have an access table I am grabbing my information from - table - > ModuleFeatures > ModuleFeatures contains Column iModule -WHICH is a lookup to a > ApplicationModules table > SELECT [ApplicationModules].[iApplicationModuleID], > [ApplicationModules].[sModuleName] FROM ApplicationModules; > > and it has an iApplication colum - which again is a lookup to a > Application table - > SELECT [ApplicationTypes].[iApplicationID], > [ApplicationTypes].[sApplicationName] FROM ApplicationTypes; > > That is the code WITHIN access. Now on coldusion - I run a simple query > to grab these features - > > SELECT iModule, iApplication, sModuleFeature FROM ModuleFeatures > ORDER BY iModule, iApplication > > > And the output is equally as simple - > > #iModule# : within #iApplication# > > #sModuleFeature# > > This outputs perfectly. And the statement above - #iModule# : within > #iApplication# was for a test. And it outputs correctly. So what it ends > up with is > 8: within 2 - > 8 in Table ApplicationModules = Medical Records > 2 in Table ApplicationTypes = Financial Applications. > > So instead of > 8: within 2 - I need it to say > Medical Records: within Financial Applications > > When I modified my simple query and alter the query to grab from both > tables - it threw everything off. > > The ONLY working solution is that I can do this... modify the output > query (shown below )-which does the trick - but is a headache. Not to > mention - I have to do that with iModule - and there are upwards of 30 > modules. Setting those by hand defeats the purpose of pulling from a > database. > > I know I there is a way to modify my simple query - but going on 3 hours > of tinkering and can not get it to work right or if I get it working - > it's not accurate. > > Sorry this is long - but I figured the more info - the better someone > can help me! > Thanks > > > > > > > > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Query Help - Access Lookup tables and query output not accurate - not working
Short Version - I realize that is VERY long - but I jsut supplied alot of information - Temporary link online - http://64.234.202.15/dbindex.cfm LookUp existing tables - need to be altered on cfquery end efficiently - 8: within 2 - 8 in Table ApplicationModules = Medical Records 2 in Table ApplicationTypes = Financial Applications. Simple question is - I am looking for an alternative to having to do this Jason Miller wrote: >In Coldusion - I run a simple query >to grab these features - > >SELECT iModule, iApplication, sModuleFeature FROM ModuleFeatures >ORDER BY iModule, iApplication > > >And the output is equally as simple - > >#iModule# : within #iApplication# > > #sModuleFeature# > >This outputs perfectly. And the statement above - #iModule# : within >#iApplication# was for a test. And it outputs correctly. So what it ends >up with is >8: within 2 - >8 in Table ApplicationModules = Medical Records >2 in Table ApplicationTypes = Financial Applications. > >So instead of >8: within 2 - I need it to say >Medical Records: within Financial Applications > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL query help
Well since your using ODBC, just use CreateODBCDate select * from main_table where datestamp > #CreateODBCDate(date,"dd/mm/")# __ steve oliver cresco technologies, inc. http://www.crescotech.com -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 7:11 AM To: CF-Talk Subject: RE: SQL query help select * from main_table where datestamp > #dateformat(date,"dd/mm/")# This is really all I want to do. The Access field is in date format. This does not seem to work and I know it isnt exactly complex so there must be something really obviously wrong. -Original Message- From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 17:25 To: CF-Talk Subject: RE: SQL query help Darren, since you are using an Access db go into your Access program. Start to create a query to return the value you want. Then look at the SQL of this query you created in Access. Access doesn't use standard SQL and has a lot of different keywords. So this will get you on the right track of what your SQL statement should look like. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 9:56 AM To: CF-Talk Subject: RE: SQL query help I think it was just that id didnt like the value in quotes !! -Original Message- From: Michael Blair [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:54 To: CF-Talk Subject: RE: SQL query help Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = "marketing" SQL = "select * from main_table where datestamp > '28/01/02'" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: "Darren Adams" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help > Hi guys > > Can anyone help, I just need to check that the date of a record in a > database is within a specific range. > > > #today# > > > select * from main_table > where 1=1 > and datestamp BETWEEN '#date#' AND #today# > > and region = '#region#'> and mater='#material#'> and verti= '#vertical#' > and target = '#target#'> and product_serv= '#product#' > > > > This query give me an error in assignment. > > I know that this should be easy but I cant think of it!!! > > Cheers, > Darren Adams > Web Developer > Marketing Department > Systems Union > > mail: [EMAIL PROTECTED] > > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
select * from main_table where datestamp > #dateformat(date,"dd/mm/")# This is really all I want to do. The Access field is in date format. This does not seem to work and I know it isnt exactly complex so there must be something really obviously wrong. -Original Message- From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 17:25 To: CF-Talk Subject: RE: SQL query help Darren, since you are using an Access db go into your Access program. Start to create a query to return the value you want. Then look at the SQL of this query you created in Access. Access doesn't use standard SQL and has a lot of different keywords. So this will get you on the right track of what your SQL statement should look like. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 9:56 AM To: CF-Talk Subject: RE: SQL query help I think it was just that id didnt like the value in quotes !! -Original Message- From: Michael Blair [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:54 To: CF-Talk Subject: RE: SQL query help Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = "marketing" SQL = "select * from main_table where datestamp > '28/01/02'" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: "Darren Adams" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help > Hi guys > > Can anyone help, I just need to check that the date of a record in a > database is within a specific range. > > > #today# > > > select * from main_table > where 1=1 > and datestamp BETWEEN '#date#' AND #today# > > and region = '#region#'>"--- Select ---"> and mater='#material#'> >and verti= '#vertical#' > and target = '#target#'>"--- Select ---"> and product_serv= '#product#' > > > > This query give me an error in assignment. > > I know that this should be easy but I cant think of it!!! > > Cheers, > Darren Adams > Web Developer > Marketing Department > Systems Union > > mail: [EMAIL PROTECTED] > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Darren, since you are using an Access db go into your Access program. Start to create a query to return the value you want. Then look at the SQL of this query you created in Access. Access doesn't use standard SQL and has a lot of different keywords. So this will get you on the right track of what your SQL statement should look like. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 9:56 AM To: CF-Talk Subject: RE: SQL query help I think it was just that id didnt like the value in quotes !! -Original Message- From: Michael Blair [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:54 To: CF-Talk Subject: RE: SQL query help Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = "marketing" SQL = "select * from main_table where datestamp > '28/01/02'" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: "Darren Adams" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help > Hi guys > > Can anyone help, I just need to check that the date of a record in a > database is within a specific range. > > > #today# > > > select * from main_table > where 1=1 > and datestamp BETWEEN '#date#' AND #today# > > and region = '#region#'> "--- Select ---"> and materi= '#material#'> and vertic= '#vertical#' > and target = '#target#'> "--- Select ---"> and product_servi= '#product#' > > > > This query give me an error in assignment. > > I know that this should be easy but I cant think of it!!! > > Cheers, > Darren Adams > Web Developer > Marketing Department > Systems Union > > mail: [EMAIL PROTECTED] > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
I think it was just that id didnt like the value in quotes !! -Original Message- From: Michael Blair [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:54 To: CF-Talk Subject: RE: SQL query help Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = "marketing" SQL = "select * from main_table where datestamp > '28/01/02'" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: "Darren Adams" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help > Hi guys > > Can anyone help, I just need to check that the date of a record in a > database is within a specific range. > > > #today# > > > select * from main_table > where 1=1 > and datestamp BETWEEN '#date#' AND #today# > > and region = '#region#'> and materia= '#material#'> and vertica= '#vertical#' > and target = '#target#'> and product_servic= '#product#' > > > > This query give me an error in assignment. > > I know that this should be easy but I cant think of it!!! > > Cheers, > Darren Adams > Web Developer > Marketing Department > Systems Union > > mail: [EMAIL PROTECTED] > > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Try reversing the mm/dd/yy into the db and see. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 10:44 AM To: CF-Talk Subject: RE: SQL query help Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = "marketing" SQL = "select * from main_table where datestamp > '28/01/02'" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: "Darren Adams" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help > Hi guys > > Can anyone help, I just need to check that the date of a record in a > database is within a specific range. > > > #today# > > > select * from main_table > where 1=1 > and datestamp BETWEEN '#date#' AND #today# > > and region = '#region#'> NEQ "--- Select ---"> and material= '#material#'> and vertical= '#vertical#' > and target = '#target#'> "--- Select ---"> and product_service= '#product#' > > > > This query give me an error in assignment. > > I know that this should be easy but I cant think of it!!! > > Cheers, > Darren Adams > Web Developer > Marketing Department > Systems Union > > mail: [EMAIL PROTECTED] > > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Even though the datestamp field is a date/time field in access I still get this error message. ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. Data Source = "marketing" SQL = "select * from main_table where datestamp > '28/01/02'" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:60) in the template file E:\Inetpub\inetsu\menutest\marketing\browse.cfm -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:44 To: CF-Talk Subject: Re: SQL query help What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: "Darren Adams" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help > Hi guys > > Can anyone help, I just need to check that the date of a record in a > database is within a specific range. > > > #today# > > > select * from main_table > where 1=1 > and datestamp BETWEEN '#date#' AND #today# > > and region = '#region#' > and material = '#material#' > and vertical = '#vertical#' > and target = '#target#' > and product_service = '#product#' > > > > This query give me an error in assignment. > > I know that this should be easy but I cant think of it!!! > > Cheers, > Darren Adams > Web Developer > Marketing Department > Systems Union > > mail: [EMAIL PROTECTED] > > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL query help
P.S have you tried just putting it into query analyzer? select * from main_table where datestamp > '12/01/2000' If so did it work? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: "Darren Adams" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 8:16 AM Subject: RE: SQL query help > Well there are a number of options which users can search by. > All the other ones work but, when they enter a date I cant compare the > dates > and get it too work. > > I tried the simple datestamp > date but, that just didnt do anything > so I > tried to do a between comparison. > > Really all I want to do is get out all the records that are later that > the > date the user specifys. > > -Original Message- > From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]] > Sent: 28 January 2002 16:12 > To: CF-Talk > Subject: RE: SQL query help > > > What are you trying to do with your where clause? > > -Original Message- > From: Darren Adams [mailto:[EMAIL PROTECTED]] > Sent: Monday, January 28, 2002 8:41 AM > To: CF-Talk > Subject: SQL query help > > > Hi guys > > Can anyone help, I just need to check that the date of a record in a > database is within a specific range. > > > #today# > > > select * from main_table > where 1=1 > and datestamp BETWEEN '#date#' AND > #today# > > and region = '#region#' > and material = > '#material#' > and vertical = > '#vertical#' > and target = '#target#' > and product_service = > '#product#' > > > > This query give me an error in assignment. > > I know that this should be easy but I cant think of it!!! > > Cheers, > Darren Adams > Web Developer > Marketing Department > Systems Union > > mail: [EMAIL PROTECTED] > > > > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL query help
What error do you get? There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown - Original Message - From: "Darren Adams" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, January 28, 2002 7:40 AM Subject: SQL query help > Hi guys > > Can anyone help, I just need to check that the date of a record in a > database is within a specific range. > > > #today# > > > select * from main_table > where 1=1 > and datestamp BETWEEN '#date#' AND #today# > > and region = '#region#' > and material = '#material#' > and vertical = '#vertical#' > and target = '#target#' > and product_service = '#product#' > > > > This query give me an error in assignment. > > I know that this should be easy but I cant think of it!!! > > Cheers, > Darren Adams > Web Developer > Marketing Department > Systems Union > > mail: [EMAIL PROTECTED] > > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Well there are a number of options which users can search by. All the other ones work but, when they enter a date I cant compare the dates and get it too work. I tried the simple datestamp > date but, that just didnt do anything so I tried to do a between comparison. Really all I want to do is get out all the records that are later that the date the user specifys. -Original Message- From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]] Sent: 28 January 2002 16:12 To: CF-Talk Subject: RE: SQL query help What are you trying to do with your where clause? -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 8:41 AM To: CF-Talk Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. #today# select * from main_table where 1=1 and datestamp BETWEEN '#date#' AND #today# and region = '#region#' and material = '#material#' and vertical = '#vertical#' and target = '#target#' and product_service = '#product#' This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
What are you trying to do with your where clause? -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 8:41 AM To: CF-Talk Subject: SQL query help Hi guys Can anyone help, I just need to check that the date of a record in a database is within a specific range. #today# select * from main_table where 1=1 and datestamp BETWEEN '#date#' AND #today# and region = '#region#' and material = '#material#' and vertical = '#vertical#' and target = '#target#' and product_service = '#product#' This query give me an error in assignment. I know that this should be easy but I cant think of it!!! Cheers, Darren Adams Web Developer Marketing Department Systems Union mail: [EMAIL PROTECTED] __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
You have shot yourself in the foot by putting the News table in the WHERE clause. If you only want records where N.Year = #currentyear# then doing an outer join makes no sense because an outer join would indicate that you ALSO want records where N.Year is NULL. Try this: SELECT R.ResortName, N.Year, N.News FROM Resorts R LEFT OUTER JOIN News N ON R.ResortID = N.ResortID WHERE N.Year = #CurrentYear# OR N.Year IS NULL Bryan Love ACP Internet Application Developer [EMAIL PROTECTED] -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 12, 2001 10:22 AM To: CF-Talk Subject: Re: SQL Query Help > I think you have you're join backwards, when you use a left outer join it > pulls all records from the table to the left in the statement. You need to > do a right join or swap the order of the tables. > > Try this: > > SELECT R.ResortName, N.Year, N.News > FROM Resorts R LEFT OUTER JOIN News N ON (R.ResortID = N.ResortID AND N.Year > = #CurrentYear#) > > this should work- Whoops, I hadn't noticed that the tables were reversed in the example. Indeed, the query I had tried was: SELECT R.ResortName, N.Year, N.News FROM Resorts R LEFT OUTER JOIN News N ON R.ResortID = N.ResortID WHERE N.Year = #CurrentYear# Doesn't return the desired results, however. It only returns rows in Resorts where there's a matching record in News for the current year. I'm pretty sure the way SQL must execute this is to do the outer join first, and THEN applies the WHERE clause, which is why I'm only getting results for the current year. Is there any kind of SQL syntax that would allow me to do a left outer join of one table with the results of another query? Excuse the mangled syntax, but something like: SELECT R.ResortName, N.Year, N.News FROM Resorts R LEFT OUTER JOIN (SELECT ResortID, Year, News FROM News N WHERE Year = #CurrentYear#) ON R.ResortID = N.ResortID Jim ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL Query Help
> I think you have you're join backwards, when you use a left outer join it > pulls all records from the table to the left in the statement. You need to > do a right join or swap the order of the tables. > > Try this: > > SELECT R.ResortName, N.Year, N.News > FROM Resorts R LEFT OUTER JOIN News N ON (R.ResortID = N.ResortID AND N.Year > = #CurrentYear#) > > this should work- Whoops, I hadn't noticed that the tables were reversed in the example. Indeed, the query I had tried was: SELECT R.ResortName, N.Year, N.News FROM Resorts R LEFT OUTER JOIN News N ON R.ResortID = N.ResortID WHERE N.Year = #CurrentYear# Doesn't return the desired results, however. It only returns rows in Resorts where there's a matching record in News for the current year. I'm pretty sure the way SQL must execute this is to do the outer join first, and THEN applies the WHERE clause, which is why I'm only getting results for the current year. Is there any kind of SQL syntax that would allow me to do a left outer join of one table with the results of another query? Excuse the mangled syntax, but something like: SELECT R.ResortName, N.Year, N.News FROM Resorts R LEFT OUTER JOIN (SELECT ResortID, Year, News FROM News N WHERE Year = #CurrentYear#) ON R.ResortID = N.ResortID Jim ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
I think you have you're join backwards, when you use a left outer join it pulls all records from the table to the left in the statement. You need to do a right join or swap the order of the tables. Try this: SELECT R.ResortName, N.Year, N.News FROM Resorts R LEFT OUTER JOIN News N ON (R.ResortID = N.ResortID AND N.Year = #CurrentYear#) this should work- -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED]] Sent: Monday, June 11, 2001 6:17 PM To: CF-Talk Subject: Re: SQL Query Help > > I could use a bit of help with what I'm guessing is a fairly > > easy query. I've got the following: > > > > Tables: > > Resorts > > NewFor > > > > Resorts Table: > > ResortID pk > > ResortName > > > > NewFor Table: > > NewForID pk > > ResortID fk > > Year > > News > > > > In the NewFor table I've got news for 1999, 2000, 2001, etc. > > for most (but not all resorts). I want to return rows for all > > resorts, along with any news there may be for the current year. > > > > So the output would be: > > ResortName, News (for only 2001, which may be null) > > > > If I do a join, generally I'm left with only resorts which > > have news records for the current year, which isn't what I'm > > after. > > If I understand correctly, you need to use an outer join rather than an > inner join. An outer join returns all rows from one table, joined to any > matching rows from the other one: > > SELECT R.ResortName, > N.News > FROM Resorts R, > News N > WHERE R.ResortID *= N.ResortID > AND N.Year = #CurrentYear# > > You can also use ANSI join syntax: > > SELECT R.ResortName, > N.News > FROM News N > LEFT OUTER JOIN Resorts R ON R.ResortID = N.ResortID > AND N.Year = #CurrentYear# > > In either case, you'll get all the resorts, and within each resort row, > you'll get any news items for that year if they exist. If they don't, the > News column will contain NULL for that row. That was my original attempt, in which case the query returns just resorts with News records for the current year. Not quite what I expected from an outer join. My kludge, until I can find the appropriate SQL approach, is to eliminate the "N.Year = #CurrentYear#" clause and sort by "ResortID, Year DESC". This returns many duplicates, with News for prior years attached to those duplicates. When looping over the results, I use only the first record and throw away the dupes (same as using CFOUTPUT's GROUP attribute in a loop). SELECT R.ResortName, N.Year, N.News FROM News N LEFT OUTER JOIN Resorts R ON R.ResortID = N.ResortID Returns: Aspen2001 Stuff... Aspen2000 More stuff... Aspen1999 Even more stuff... Breckenridge 1999 Old news... Keystone NULL NULL Steamboat2000 Some news... Vail 2001 News... Vail 1999 More SELECT R.ResortName, N.Year, N.News FROM News N LEFT OUTER JOIN Resorts R ON R.ResortID = N.ResortID WHERE N.Year = #CurrentYear# Returns: Aspen2001 Stuff... Vail 2001 News... I'm trying to find a way of getting to: Aspen2001 Stuff... Breckenridge NULL NULL Keystone NULL NULL SteamboatNULL NULL Vail 2001 News... Or, just as well, would be to return the _latest_ News record (and NULL for resorts that have none), and I could suppress the news output if the year isn't current. Thanks, Jim ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL Query Help
> > I could use a bit of help with what I'm guessing is a fairly > > easy query. I've got the following: > > > > Tables: > > Resorts > > NewFor > > > > Resorts Table: > > ResortID pk > > ResortName > > > > NewFor Table: > > NewForID pk > > ResortID fk > > Year > > News > > > > In the NewFor table I've got news for 1999, 2000, 2001, etc. > > for most (but not all resorts). I want to return rows for all > > resorts, along with any news there may be for the current year. > > > > So the output would be: > > ResortName, News (for only 2001, which may be null) > > > > If I do a join, generally I'm left with only resorts which > > have news records for the current year, which isn't what I'm > > after. > > If I understand correctly, you need to use an outer join rather than an > inner join. An outer join returns all rows from one table, joined to any > matching rows from the other one: > > SELECT R.ResortName, > N.News > FROM Resorts R, > News N > WHERE R.ResortID *= N.ResortID > AND N.Year = #CurrentYear# > > You can also use ANSI join syntax: > > SELECT R.ResortName, > N.News > FROM News N > LEFT OUTER JOIN Resorts R ON R.ResortID = N.ResortID > AND N.Year = #CurrentYear# > > In either case, you'll get all the resorts, and within each resort row, > you'll get any news items for that year if they exist. If they don't, the > News column will contain NULL for that row. That was my original attempt, in which case the query returns just resorts with News records for the current year. Not quite what I expected from an outer join. My kludge, until I can find the appropriate SQL approach, is to eliminate the "N.Year = #CurrentYear#" clause and sort by "ResortID, Year DESC". This returns many duplicates, with News for prior years attached to those duplicates. When looping over the results, I use only the first record and throw away the dupes (same as using CFOUTPUT's GROUP attribute in a loop). SELECT R.ResortName, N.Year, N.News FROM News N LEFT OUTER JOIN Resorts R ON R.ResortID = N.ResortID Returns: Aspen2001 Stuff... Aspen2000 More stuff... Aspen1999 Even more stuff... Breckenridge 1999 Old news... Keystone NULL NULL Steamboat2000 Some news... Vail 2001 News... Vail 1999 More SELECT R.ResortName, N.Year, N.News FROM News N LEFT OUTER JOIN Resorts R ON R.ResortID = N.ResortID WHERE N.Year = #CurrentYear# Returns: Aspen2001 Stuff... Vail 2001 News... I'm trying to find a way of getting to: Aspen2001 Stuff... Breckenridge NULL NULL Keystone NULL NULL SteamboatNULL NULL Vail 2001 News... Or, just as well, would be to return the _latest_ News record (and NULL for resorts that have none), and I could suppress the news output if the year isn't current. Thanks, Jim ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
> I could use a bit of help with what I'm guessing is a fairly > easy query. I've got the following: > > Tables: > Resorts > NewFor > > Resorts Table: > ResortID pk > ResortName > > NewFor Table: > NewForID pk > ResortID fk > Year > News > > In the NewFor table I've got news for 1999, 2000, 2001, etc. > for most (but not all resorts). I want to return rows for all > resorts, along with any news there may be for the current year. > > So the output would be: > ResortName, News (for only 2001, which may be null) > > If I do a join, generally I'm left with only resorts which > have news records for the current year, which isn't what I'm > after. If I understand correctly, you need to use an outer join rather than an inner join. An outer join returns all rows from one table, joined to any matching rows from the other one: SELECT R.ResortName, N.News FROMResorts R, News N WHERE R.ResortID *= N.ResortID AND N.Year = #CurrentYear# You can also use ANSI join syntax: SELECT R.ResortName, N.News FROMNews N LEFT OUTER JOIN Resorts R ON R.ResortID = N.ResortID AND N.Year = #CurrentYear# In either case, you'll get all the resorts, and within each resort row, you'll get any news items for that year if they exist. If they don't, the News column will contain NULL for that row. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
You need to do an outer join- select resortname, news from resorts r LEFT JOIN newfor nf ON r.resortid = nf.resortid where year = YEAR(GETDATE()) -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED]] Sent: Monday, June 11, 2001 12:16 PM To: CF-Talk Subject: OT: SQL Query Help I could use a bit of help with what I'm guessing is a fairly easy query. I've got the following: Tables: Resorts NewFor Resorts Table: ResortID pk ResortName NewFor Table: NewForID pk ResortID fk Year News In the NewFor table I've got news for 1999, 2000, 2001, etc. for most (but not all resorts). I want to return rows for all resorts, along with any news there may be for the current year. So the output would be: ResortName, News (for only 2001, which may be null) If I do a join, generally I'm left with only resorts which have news records for the current year, which isn't what I'm after. Thanks for any assistance, Jim ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
you should try to avoid using SELECT * in a production environment. it's unnecessary process utilization. it's more efficient for SQL to look up specified row names. might take longer to type, but it's the better way to go. ;) -Original Message- From: Kevin Mansel [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 2:05 PM To: CF-Talk Subject: RE: SQL query help do a group by query SELECT * FROM Bars GROUP BY BarColor hth kevin ~ Kevin Mansel Web Developer Fox Communications [EMAIL PROTECTED] DL : 425-649-1321 C : 425-346-7221 -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12" 2 bar blue6" 3 bar blue5" 4 bar red 12" 5 bar red 6" 6 bar red 5" I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
SELECT distinct(color) as color WHERE name = 'bar' chris olive, cio cresco technologies [EMAIL PROTECTED] http://www.crescotech.com -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 4:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12" 2 bar blue6" 3 bar blue5" 4 bar red 12" 5 bar red 6" 6 bar red 5" I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
select DISTINCT color from... Tony -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12" 2 bar blue6" 3 bar blue5" 4 bar red 12" 5 bar red 6" 6 bar red 5" I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
SELECT DISTINCT color from tableName where name='bar' -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12" 2 bar blue6" 3 bar blue5" 4 bar red 12" 5 bar red 6" 6 bar red 5" I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Chad, you'd want to do this: SELECT DISTINCT COLOR FROM tblWhatever WHERE NAME = 'bar' david -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12" 2 bar blue6" 3 bar blue5" 4 bar red 12" 5 bar red 6" 6 bar red 5" I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Chad, OOPS! fumble-fingered today, meant SELECT DISTINCT COLOR ... Ben -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12" 2 bar blue6" 3 bar blue5" 4 bar red 12" 5 bar red 6" 6 bar red 5" I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Chad, SELECT DISTINCT NAME ... Ben -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12" 2 bar blue6" 3 bar blue5" 4 bar red 12" 5 bar red 6" 6 bar red 5" I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
do a group by query SELECT * FROM Bars GROUP BY BarColor hth kevin ~ Kevin Mansel Web Developer Fox Communications [EMAIL PROTECTED] DL : 425-649-1321 C : 425-346-7221 -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12" 2 bar blue6" 3 bar blue5" 4 bar red 12" 5 bar red 6" 6 bar red 5" I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
You can either use the GROUP BY statement or do a SELECT DISTINCT(Color) FROM WHEREEVER WHERE WHATEVER hth, Bill -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 1:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12" 2 bar blue6" 3 bar blue5" 4 bar red 12" 5 bar red 6" 6 bar red 5" I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Chad, Use the DISTINCT keyword: SELECT DISTINCT COLOR FROM Yourtable .. > -Original Message- > From: Chad Gray [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, May 08, 2001 4:16 PM > To: CF-Talk > Subject: SQL query help > > > I have a database similar to this: > > SKU NAMECOLOR STYLE > 1 bar blue12" > 2 bar blue6" > 3 bar blue5" > 4 bar red 12" > 5 bar red 6" > 6 bar red 5" > > > I want to find all the colors that 'bar' comes in. > > If i do a query on this to find all the colors for the NAME 'bar'. I get: > blue > blue > blue > red > red > red > > How do i get results like this? > blue > red > > Thanks in advanced > Chad > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL query help
Try SELECT DISTINCT color WHERE NAME = 'bar' Steven Semrau SRA International, Inc. Senior Member, Professional Staff [EMAIL PROTECTED] [EMAIL PROTECTED] Com: (703) 805-1095 DSN: (703) 655-1095 -Original Message- From: Chad Gray [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 4:16 PM To: CF-Talk Subject: SQL query help I have a database similar to this: SKU NAMECOLOR STYLE 1 bar blue12" 2 bar blue6" 3 bar blue5" 4 bar red 12" 5 bar red 6" 6 bar red 5" I want to find all the colors that 'bar' comes in. If i do a query on this to find all the colors for the NAME 'bar'. I get: blue blue blue red red red How do i get results like this? blue red Thanks in advanced Chad ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
Thanks very much Philip Putting the times in worked like a treat! -= Ed > -Original Message- > From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]] > Sent: Monday, February 19, 2001 12:06 PM > To: CF-Talk > Subject: RE: SQL Query Help > > > > I don't know if any of you know how to do it but I'm trying to > get the SQL > > query pasted below to include the two dates, at the moment it > > doesn't return > > data from the two dates specified. ie It returns queries starting > > Jan 02 and ending 19 Feb > > > > SELECT DateAdded, EmailA > > FROMeShot > > WHERE ( DateAdded > > BETWEEN '2001/Jan/01' > > AND '2001/Feb/20' ) > > AND EmailA LIKE '%@%' > > AND ConfCode LIKE '%cc0243%' > > ORDER BYDateAdded > > What you've got to remember is that a straight date is > effectively midnight > at that date and because a DateTime field is exactly that, you have to > consider a BETWEEN to return the results you specify > > If you want to get the results between 1 Jan 2001 and 19 Feb 2001, you can > either do > BETWEEN '1 Jan 2001' AND '20 Feb 2001' > or > BETWEEN '1 Jan 2001 00:00:00' AND '19 Feb 2001 23:59:59' > > It should always return the whole day of the Low Date in a > BETWEEN, if it's > not, you should check your data and ensure there is actually data there in > the first place > > Philip Arnold > Director > Certified ColdFusion Developer > ASP Multimedia Limited > T: +44 (0)20 8680 1133 > > "Websites for the real world" > > ** > This email and any files transmitted with it are confidential and > intended solely for the use of the individual or entity to whom they > are addressed. If you have received this email in error please notify > the system manager. > ** > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
You could always use the DATEADD function to add a day to the later date and take a day off the earlier date. -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: Edward Chanter [mailto:[EMAIL PROTECTED]] Sent: 19 February 2001 12:04 To: CF-Talk Subject: RE: SQL Query Help I would do but the dates are input into a form by our client. They are under the assumption that selecting the two dates will include them I'm trying to find a clean way to get the system to do that without having to spend untold time explaining it to the client on the web form.. -= Ed > -Original Message- > From: Russell Brown [mailto:[EMAIL PROTECTED]] > Sent: Monday, February 19, 2001 11:53 AM > To: CF-Talk > Subject: RE: SQL Query Help > > > Change the dates to a day earlier and a day later ? > > Russell Brown > Internet Application Developer > Freeserve.com Plc, PO Box 452, Leeds LS2 7EY > Telephone: 0113 207 1203 > > > -Original Message- > From: Edward Chanter [mailto:[EMAIL PROTECTED]] > Sent: 19 February 2001 11:48 > To: CF-Talk > Subject: OT: SQL Query Help > > > I don't know if any of you know how to do it but I'm trying to get the SQL > query pasted below to include the two dates, at the moment it > doesn't return > data from the two dates specified. ie It returns queries starting > Jan 02 and > ending 19 Feb > > :-( > > SELECTDateAdded, EmailA > FROM eShot > WHERE ( DateAdded > BETWEEN '2001/Jan/01' > AND '2001/Feb/20' ) > AND EmailA LIKE '%@%' > AND ConfCode LIKE '%cc0243%' > ORDER BY DateAdded > > > All I can say is: HELP > > Any and all comments would be most gratefully appreciated. > > -= Ed > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
> I don't know if any of you know how to do it but I'm trying to get the SQL > query pasted below to include the two dates, at the moment it > doesn't return > data from the two dates specified. ie It returns queries starting > Jan 02 and ending 19 Feb > > SELECTDateAdded, EmailA > FROM eShot > WHERE ( DateAdded > BETWEEN '2001/Jan/01' > AND '2001/Feb/20' ) > AND EmailA LIKE '%@%' > AND ConfCode LIKE '%cc0243%' > ORDER BY DateAdded What you've got to remember is that a straight date is effectively midnight at that date and because a DateTime field is exactly that, you have to consider a BETWEEN to return the results you specify If you want to get the results between 1 Jan 2001 and 19 Feb 2001, you can either do BETWEEN '1 Jan 2001' AND '20 Feb 2001' or BETWEEN '1 Jan 2001 00:00:00' AND '19 Feb 2001 23:59:59' It should always return the whole day of the Low Date in a BETWEEN, if it's not, you should check your data and ensure there is actually data there in the first place Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
I would do but the dates are input into a form by our client. They are under the assumption that selecting the two dates will include them I'm trying to find a clean way to get the system to do that without having to spend untold time explaining it to the client on the web form.. -= Ed > -Original Message- > From: Russell Brown [mailto:[EMAIL PROTECTED]] > Sent: Monday, February 19, 2001 11:53 AM > To: CF-Talk > Subject: RE: SQL Query Help > > > Change the dates to a day earlier and a day later ? > > Russell Brown > Internet Application Developer > Freeserve.com Plc, PO Box 452, Leeds LS2 7EY > Telephone: 0113 207 1203 > > > -Original Message- > From: Edward Chanter [mailto:[EMAIL PROTECTED]] > Sent: 19 February 2001 11:48 > To: CF-Talk > Subject: OT: SQL Query Help > > > I don't know if any of you know how to do it but I'm trying to get the SQL > query pasted below to include the two dates, at the moment it > doesn't return > data from the two dates specified. ie It returns queries starting > Jan 02 and > ending 19 Feb > > :-( > > SELECTDateAdded, EmailA > FROM eShot > WHERE ( DateAdded > BETWEEN '2001/Jan/01' > AND '2001/Feb/20' ) > AND EmailA LIKE '%@%' > AND ConfCode LIKE '%cc0243%' > ORDER BY DateAdded > > > All I can say is: HELP > > Any and all comments would be most gratefully appreciated. > > -= Ed > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Help
Change the dates to a day earlier and a day later ? Russell Brown Internet Application Developer Freeserve.com Plc, PO Box 452, Leeds LS2 7EY Telephone: 0113 207 1203 -Original Message- From: Edward Chanter [mailto:[EMAIL PROTECTED]] Sent: 19 February 2001 11:48 To: CF-Talk Subject: OT: SQL Query Help I don't know if any of you know how to do it but I'm trying to get the SQL query pasted below to include the two dates, at the moment it doesn't return data from the two dates specified. ie It returns queries starting Jan 02 and ending 19 Feb :-( SELECT DateAdded, EmailA FROMeShot WHERE ( DateAdded BETWEEN '2001/Jan/01' AND '2001/Feb/20' ) AND EmailA LIKE '%@%' AND ConfCode LIKE '%cc0243%' ORDER BYDateAdded All I can say is: HELP Any and all comments would be most gratefully appreciated. -= Ed ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
