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 Alias problem in SELECT with HAVING clause
Date Fri, 08 Sep 2006 08:14:29 GMT
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.

Any ideas on how to deal with this?

Regards,
Robert

Mime
View raw message