db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Enyedi <robert.eny...@intland.com>
Subject Re: Alias problem in SELECT with HAVING clause
Date Fri, 08 Sep 2006 09:03:35 GMT
Bernt,

Thanks for the workaround. It is working as expected and it doesn't 
complicate the query too much.

Regards,
Robert

Bernt M. Johnsen wrote:
> 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
>>     
>
>
>   


Mime
View raw message