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
