yep, I missed the "MS-SQL" req in the initial question and wrote the query for Oracle DB. trunc -- removes the time portion in the date object
-----Original Message----- From: Dina Hess [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 9:05 AM To: SQL Subject: Re: getting results from 2 tables Trunc() is an Oracle function, isn't it? ----- Original Message ----- From: "Andy Ewings" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Wednesday, February 26, 2003 10:13 AM Subject: RE: getting results from 2 tables > yup - but good practice is to use join notation to link tables - so > becomes..... > > SELECT ides.ItemDesc, o.orderID, count(*) > FROM Orders o > INNER JOIN order_item oi ON (o.OrderID = oi.OrderID) > INNER JOIN Itemdescription ides ON (oi.ItemID = ides.ItemID) > WHERE o.Date = trunc(sysdate) > GROUP BY ides.ItemDesc, o.orderID > > -----Original Message----- > From: Alex Ninan [mailto:[EMAIL PROTECTED] > Sent: 26 February 2003 16:03 > To: SQL > Subject: RE: getting results from 2 tables > > > SELECT ides.ItemDesc, o.orderID, count(*) > FROM Orders o, Itemdescription ides, order_item oi > WHERE o.OrderID = oi.OrderID > AND ides.ItemID = oi.ItemID > AND o.Date = trunc(sysdate) > GROUP BY ides.ItemDesc, o.orderID > > This might help you out... > > -----Original Message----- > From: Dave and Cathy [mailto:[EMAIL PROTECTED] > Sent: Wednesday, February 26, 2003 6:13 AM > To: SQL > Subject: getting results from 2 tables > > > Hi All, > > I have three SQL tables: > Orders - contains Date, OrderID, > ItemDescription - contains ItemID and ItemDesc, > Order_Items - a table that links the two on OrderID and ItemID > (many-to-many). > > I need to produce a report of how many items are in today's Orders, > grouped by ItemDesc (with a count), in ItemDesc Order. > > Seems like a common-enough requirement, but I can't seem to find a > result other than doing one SELECT then looping thru the results with > another select. > > Is there a one-query solution please? > > TIA > Dave > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
