A trouble with aliases in JOIN statement.

[Ruslan Osmanov]

I have two tables: rh_forum_message and rh_user. Both have a user_id field.

Actually, user_id in rh_forum_message is set as
FOREIGN KEY(user_id) REFERENCES rh_user(user_id)

The aim is to retrieve user_id along with other fields of the two tables.
My buggy query is:

SELECT `rh_forum_message`.`user_id` , `username` , `forum_id` , `message_id` , `parent_message_id` , `subject` , `message_text` , `avatar_image` , `file_size` , UNIX_TIMESTAMP( `message_date` ) AS `message_date`
FROM `rh_forum_message` AS M
JOIN `rh_user` AS U ON M.`user_id` = U.`user_id`
WHERE `message_id` =1
OR `parent_message_id` =1
ORDER BY `message_date` ASC

In response MySQL emits `#1054 - Unknown column 'rh_forum_message.user_id' in 'field list'` while the following (without aliases) works fine.


SELECT `rh_forum_message`.`user_id` , `username` , `forum_id` , `message_id` , `parent_message_id` , `subject` , `message_text` , `avatar_image` , `file_size` , UNIX_TIMESTAMP( `message_date` ) AS `message_date`
FROM `rh_forum_message`
JOIN `rh_user` ON `rh_forum_message`.`user_id` = `rh_user`.`user_id`
WHERE `message_id` =1
OR `parent_message_id` =1
ORDER BY `message_date` ASC

Could you kindly tell me what is wrong?

Thanks.