JOINs with result of aggregate function fails with error #1054

2006-02-21 Thread Guillaume Boissiere
This must have been asked before but I could not find the answer searching
the list archives.
I have a simple table:
CREATE TABLE `license` (
  `id` int(11) NOT NULL auto_increment,
  `firstname` varchar(100) NOT NULL default '',
  `lastname` varchar(100) NOT NULL default '',
  `host_address` varchar(100) NOT NULL default '',
  `is_visible` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;


This first query works (MySQL 4.0.25):

SELECT l.id, MAX(l.id) AS maxid, l.host_address
FROM `license` l
WHERE l.is_visible='1'
GROUP BY l.host_address
ORDER BY maxid DESC

but this second query fails with the error:  #1054 - Unknown column 'maxid'
in 'on clause'

SELECT l.id, MAX(l.id) AS maxid, l.host_address, l2.firstname, l2.lastname
FROM `license` l
INNER JOIN license l2 ON l2.id=maxid
WHERE l.is_visible='1'
GROUP BY l.host_address
ORDER BY maxid DESC

Is there a way to do a join with the result of an aggregate function (in
this case MAX(id))
in one query, or do I have to use multiple queries for this?

Thanks in advance!

Guillaume


Re: JOINs with result of aggregate function fails with error #1054

2006-02-21 Thread gerald_clark

Guillaume Boissiere wrote:


This must have been asked before but I could not find the answer searching
the list archives.
I have a simple table:
CREATE TABLE `license` (
 `id` int(11) NOT NULL auto_increment,
 `firstname` varchar(100) NOT NULL default '',
 `lastname` varchar(100) NOT NULL default '',
 `host_address` varchar(100) NOT NULL default '',
 `is_visible` tinyint(4) NOT NULL default '1',
 PRIMARY KEY  (`id`)
) TYPE=MyISAM;


This first query works (MySQL 4.0.25):

SELECT l.id, MAX(l.id) AS maxid, l.host_address
FROM `license` l
WHERE l.is_visible='1'
GROUP BY l.host_address
ORDER BY maxid DESC

but this second query fails with the error:  #1054 - Unknown column 'maxid'
in 'on clause'

SELECT l.id, MAX(l.id) AS maxid, l.host_address, l2.firstname, l2.lastname
FROM `license` l
INNER JOIN license l2 ON l2.id=maxid
WHERE l.is_visible='1'
GROUP BY l.host_address
ORDER BY maxid DESC

Is there a way to do a join with the result of an aggregate function (in
this case MAX(id))
in one query, or do I have to use multiple queries for this?

Thanks in advance!

Guillaume

 

You cannot join on an aggregate function. The value of maxid cannot be 
determined until after the join is completed.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



Re: JOINs with result of aggregate function fails with error #1054

2006-02-21 Thread SGreen
Guillaume Boissiere [EMAIL PROTECTED] wrote on 02/21/2006 02:23:29 
PM:

 This must have been asked before but I could not find the answer 
searching
 the list archives.
 I have a simple table:
 CREATE TABLE `license` (
   `id` int(11) NOT NULL auto_increment,
   `firstname` varchar(100) NOT NULL default '',
   `lastname` varchar(100) NOT NULL default '',
   `host_address` varchar(100) NOT NULL default '',
   `is_visible` tinyint(4) NOT NULL default '1',
   PRIMARY KEY  (`id`)
 ) TYPE=MyISAM;
 
 
 This first query works (MySQL 4.0.25):
 
 SELECT l.id, MAX(l.id) AS maxid, l.host_address
 FROM `license` l
 WHERE l.is_visible='1'
 GROUP BY l.host_address
 ORDER BY maxid DESC
 
 but this second query fails with the error:  #1054 - Unknown column 
'maxid'
 in 'on clause'
 
 SELECT l.id, MAX(l.id) AS maxid, l.host_address, l2.firstname, 
l2.lastname
 FROM `license` l
 INNER JOIN license l2 ON l2.id=maxid
 WHERE l.is_visible='1'
 GROUP BY l.host_address
 ORDER BY maxid DESC
 
 Is there a way to do a join with the result of an aggregate function (in
 this case MAX(id))
 in one query, or do I have to use multiple queries for this?
 
 Thanks in advance!
 
 Guillaume

You you will have to use two queries. You might have been able to use a 
subquery but your version doesn't support them. 

The reason for the error is that the MAX() function isn't evaluated until 
you get to the GROUP BY portion of the query. This is AFTER all tables are 
JOIN-ed and all of the WHERE restrictions have been applied. The column 
name `maxid` won't exist until the MAX() value has been calculated so ... 
you have nothing to join to. Thus the error.

sorry!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine