Rhino:
Thanks for the feedback.
The query is generated by a home-grown tool, which allows the users to build
ad-hoc reports. For this reason, and because there are aliases in use (in
this case, two agents are joined to the underlying table), it would be
possible for a similar query that would group by both "AgentCreatedBy" and
"AgentAssignedTo" - which are both the same column in the underlying table,
joined to by two separate foreign keys. So, that is the reason for all the
'extra' table qualifiers.
I will try your suggestion of using the column name vs. the alias name in
the GROUP BY clause, and see if that works. Interestingly, however, if I
remove the table qualifier, and just do the group by on the aliased column,
it works, as such:
GROUP BY `Product`, `AssignedTo`
Geoff Thompson
Avaion Support
[EMAIL PROTECTED]
http://www.avaion.com
> -----Original Message-----
> From: Rhino [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 23, 2004 10:26 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: Alias query problem in 4.1.7?
>
> Try changing your GROUP BY to use the column name of the second column in
> the SELECT, not the alias of the second column name, i.e.
>
> GROUP BY `Product`.`Product`, `AssignedToAgent`.`AgentName`
>
> That should work on any version of MySQL. I don't think you're allowed to
> use aliases in a GROUP BY, only actual column names. Then again, I am
> mostly
> a DB2 person and the MySQL dialect of SQL may allow aliases in a GROUP BY
> for all I know.
>
> I'm suprised that the alias worked on 3.2.3: are you sure you have
> reproduced the exact query that works on 3.2.3? I'm afraid I don't have
> either a 3.2.3 or 4.1.7 system so I can't try any of this for myself to
> see.
>
> By the way, did you realize that your query is substantially longer than
> it
> needs to be? You really only need to qualify column names with table names
> if you are doing a join of two or more tables and even then, you only need
> to qualify column names that occur in two or more of the tables read by
> the
> query. That would also eliminate the need for you to write aliases for
> some
> of your table names at all, further shortening the query. In your query,
> it
> appears that only the 'ProductKey' column occurs in more than one of the
> tables so your query could be as short as this:
>
> SELECT `Product`, `AgentName` AS `AssignedTo`, sum(`Inquiries`) AS
> `Inquiries`
> FROM `Inquiry` INNER JOIN `Product` ON `Inquiry`.`ProductKey` =
> `Product`.`ProductKey`
> INNER JOIN `Agent` ON `AssignedToKey` = `AgentKey`
> INNER JOIN `DateDim` ON `DateOpenKey` = `DateDimKey`
> WHERE `Year` = '2003'
> GROUP BY `Product`, `AssignedTo`;
>
> Then again, perhaps it is your shop standard to fully qualify all column
> names in queries; if so, you should follow your shop standard ;-) More
> likely, you are probably using some sort of query generating tool in which
> case you probably don't have a choice in the matter.
>
> Rhino
>
>
>
> ----- Original Message -----
> From: "Geoffrey R. Thompson" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Tuesday, November 23, 2004 12:11 AM
> Subject: Alias query problem in 4.1.7?
>
>
> 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:
>
>
>
> 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`;
>
>
>
> 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?
>
>
>
> Any help would be greatly appreciated.
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
