db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Alias problem in SELECT with HAVING clause
Date Fri, 08 Sep 2006 08:48:42 GMT
Robert Enyedi wrote:
> For the following database structure:
> 
> CREATE TABLE users (
>    id INT PRIMARY KEY,
>    email VARCHAR(64)
> );
> 
> CREATE TABLE notification (
>    id INT PRIMARY KEY,
>    user_id INT,
>    count INT NOT NULL
> );
> 
> I use this query:
> 
> SELECT users.email, users.id AS user_id
> FROM users
> INNER JOIN notification ON notification.user_id=users.id
> GROUP BY users.email,users.id
> HAVING SUM(notification.count) > 0
> 
> And this is the error message I get:
> 
> "ERROR 42X04: Column 'USERS.USER_ID' is either not in any table in the
> FROM list or appears within a join specification and is outside the
> scope of the join specification or appears in a HAVING clause and is not
> in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then
> 'USERS.USER_ID' is not a column in the target table."
> 
> I need to use the user_id alias. It seems to me like an internal error
> because when the HAVING clause is removed the query executes without
> errors.

Obviously a bug, since USERS.USER_ID does not exist....

> Any ideas on how to deal with this?

A hack to get around could maybe be

SELECT * FROM (SELECT users.email, users.id
		FROM users
		INNER JOIN notification ON notification.user_id=users.id
		GROUP BY users.email,users.id
		HAVING SUM(notification.count) > 0) AS t(email,user_id);

> 
> Regards,
> Robert


-- 
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message