Michael Stassen wrote: > > 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; >
Thanks for the tip. > You don't say, but I expect you get > > ERROR 1054 (42S22): Unknown column 'AssignedToAgent.AssignedTo' in > 'group > statement' > That is correct. > 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? I will. As I mentioned earlier, I did determine that the query would run by removing the table name. I just wondered why something that did work in one release ceased working in another. I generally prefer to 'know why' vs. just changing code when I see something like this. FYI - the query is generated through a reporting engine that we have written, which is why the 'heavy-handed' use of table names that are seemingly unnecessary. Depending on the column names selected by the user in the reporting tool, the query engine sometimes builds queries with ambiguous names without the table prefix. Having said that, the alias name will be sufficiently un-ambiguous, so it will definitely work for our purposes. > > 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. Thanks - I will keep tabs on the response. Geoff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
