Geoffrey R. Thompson wrote:
I have an interesting problem that I cannot find any clues to in the MySQL documentation. The following query works in 3.2.3, but does not work in 4.1.7:
You would increase your chances of getting a useful answer if you would take the time to reformat your query to be easily readable, as I have done for you below.
SELECT Product.Product, AssignedToAgent.AgentName AS AssignedTo,
sum(Inquiries) AS Inquiries FROM Inquiry INNER JOIN Product ON Inquiry.ProductKey = Product.ProductKey INNER JOIN Agent AS AssignedToAgent ON Inquiry.AssignedToKey = AssignedToAgent.AgentKey INNER JOIN DateDim AS DateOpen ON Inquiry.DateOpenKey = DateOpen.DateDimKey WHERE DateOpen.Year = '2003' GROUP BY Product.Product, AssignedToAgent.AssignedTo;
You don't say, but I expect you get
ERROR 1054 (42S22): Unknown column 'AssignedToAgent.AssignedTo' in 'group statement'
Table AssignedToAgent (AKA Agent) does not have a column named AssignedTo. It has a column named AgentName. AssignedTo is an alias which means AssignedToAgent.AgentName. I would expect AssignedToAgent.AgentName and AssignedTo to work in the GROUP BY. I wouldn't have expected AssignedToAgent.AssignedTo to work, but I have confirmed that it does work in 4.0.20.
It appears that if I take the table alias "AssignedToAgent" out of the GROUP BY clause (leaving just the column alias "AssignedTo"), the query will then work in 4.1.7 - even though the table alias does not present a problem in 3.2.3. Any ideas why?
As I said, I'm surprised it ever worked. In any case, having made "AssignedTo" an alias for the column in question, why not simply use that alias instead of trying to qualify it with an unnecessary table name?
Any help would be greatly appreciated.
Since it does work in 4.0.20, and I cannot find the change documented, I've gone ahead and submitted this as a bug report <http://bugs.mysql.com/bug.php?id=6818>, though I would not be surprised to hear that this was intentional.
Geoff Thompson
Avaion Support
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> http://www.avaion.com <http://www.avaion.com/>
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
