How to correctly compose a SQL query with JOIN?

Question

MySQL

There is a request (performed in a procedure, parameters are passed: send_to, time_from, out_limit):

SELECT 
msgs.text, 
msgs.time, 
users.nickname,
roles.nickname_color,
roles.message_color
FROM msgs 
JOIN users
ON users.account_id=msgs.user_id 
JOIN roles 
ON users.role_id=roles.role_id 
WHERE msgs.send_to=send_to AND msgs.time>time_from
LIMIT out_limit

Some rows from the msgs table are ignored, I tried it like this:

SELECT 
msgs.text, 
msgs.time, 
users.nickname,
roles.nickname_color,
roles.message_color
FROM msgs 
JOIN users
ON users.account_id=msgs.user_id 
FULL OUTER JOIN roles 
ON users.role_id=roles.role_id 
WHERE msgs.send_to=send_to AND msgs.time>time_from
LIMIT out_limit

Syntax error on "FULL OUTER JOIN users...".
I need to display ALL lines from msgs that satisfy the condition:

msgs.send_to=send_to AND msgs.time>time_from

And the obligatory addition of data from the users table (if there is no match between users.account_id and msgs.user_id - ignore) and not mandatory from roles (if there is no match between users.role_id and roles.role_id, then leave NULL in the selected fields from roles).
What am I doing wrong?

Answer 1

Replace the first join with left join, the second one is also necessary.