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
