September 29, 2009 at 2:23 pm
SELECT top 5 Customers.ContactName, Orders.OrderDate,
(UnitPrice * Quantity) as Order_Cost
FROM Customers, Orders, [Order Details]
WHERE Customers.CustomerID = Orders.CustomerID
AND Orders.OrderID = [Order Details].OrderID
AND (UnitPrice * Quantity) >= 25000
ORDER BY Orders.OrderDate DESC;
The current code yields no results so I tried adding SELECT top 5 * and I know I need to add HAVING SUM(UnitPrice * (1 - Discount) * Quantity) > 25000 then my order by, but because of my WHERE clause I get an outer reference error. I am very new at this and would very much appreciate it if someone could explain to me what I need to do.
I do not want to just have the correct code shown to me though that would be nice. I really want an explanation on how to make this work so I can learn from the mistakes I have made. Again any help is very much appreciated as I really want to learn SQL.
September 29, 2009 at 3:01 pm
The first thing I'd do with this would be to switch to ANSI style joins, where you explicitly list the join type and the join math in the From clause, instead of having the join data in the Where clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2009 at 3:43 pm
GSquared (9/29/2009)
The first thing I'd do with this would be to switch to ANSI style joins
I got into a bad habit of using commas for joins, until I realized what a pain in the rear end it is if I had to change one to an outer join. What exactly do you call it when commas are used like the above? And is there a way to make an outer join with them?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 29, 2009 at 3:58 pm
Greg Snidow (9/29/2009)
GSquared (9/29/2009)
The first thing I'd do with this would be to switch to ANSI style joinsI got into a bad habit of using commas for joins, until I realized what a pain in the rear end it is if I had to change one to an outer join. What exactly do you call it when commas are used like the above? And is there a way to make an outer join with them?
I think some people call that notation in-fixed operators. Either way, once you have moved to SQL Server 2005 and greater - using that syntax for outer joins is not possible (except if you set compatibility level back to 2000).
I agree with Gus - switch to the newer style.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 2, 2009 at 3:22 pm
shalyn_bronson22 (9/29/2009)
SELECT top 5 Customers.ContactName, Orders.OrderDate,(UnitPrice * Quantity) as Order_Cost
FROM Customers, Orders, [Order Details]
WHERE Customers.CustomerID = Orders.CustomerID
AND Orders.OrderID = [Order Details].OrderID
AND (UnitPrice * Quantity) >= 25000
ORDER BY Orders.OrderDate DESC;
The current code yields no results so I tried adding SELECT top 5 * and I know I need to add HAVING SUM(UnitPrice * (1 - Discount) * Quantity) > 25000 then my order by, but because of my WHERE clause I get an outer reference error. I am very new at this and would very much appreciate it if someone could explain to me what I need to do.
I do not want to just have the correct code shown to me though that would be nice. I really want an explanation on how to make this work so I can learn from the mistakes I have made. Again any help is very much appreciated as I really want to learn SQL.
And once you switch to the new style joins, examine the data in the actual table. There are no rows which meet the criteria of Price * quantity > 25000. Knock a 0 off the end of that and you'll get some results.
This is my guess as to the query you are trying to end up with:
SELECT TOP 5
C.ContactName, O.OrderDate, SUM((UnitPrice * (1 - Discount)) * Quantity) AS Order_Cost
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN [Order Details] OD ON O.OrderID = OD.OrderID
--WHERE (UnitPrice * Quantity) >= 2500
GROUP BY C.ContactName, O.OrderDate
HAVING SUM((UnitPrice * (1 - Discount)) * Quantity) > 2500
ORDER BY O.OrderDate DESC ;
Note that when you use aggregate functions (SUM) you need to use the group by field for any other fields you want returned. This query uses the joins that the above posters are referring to.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply