mysql selects with joins with foreign keys when using a supplied id

Also, if this isn't the right place then i'll move it to stack overflow. I posted it here, since when i had a question about mysql over there, they said that this is where such questions should be asked.

The problem that i'm having is with my chat system. I have a normal chat table and then i also have on that is used for private messages. As such the private message chat table only contains the message id that was private, and also the person who was supposed to receive it.

the table schema is something like this.

chat(
id int unsigned not null,
sayer_id int unsigned not null,
message_type tinyint unsigned not null,
message varchar(150) not null,
timesent timestamp on update current_timestamp,
);

the indexes are on id, and timesent. Primary is id, and also a normal index on timesent. The second table is just.

chat_private(
message_id int unsigned not null,
target_id int unsigned not null
)

the primary is on message id and is also a foreign key. target id currently doesn't have one at the moment but i may put a normal index on it.

Now then, the query that i'm attempting to do is something like SELECT message, timesent FROM chat WHERE timesent>=last_update AND target_id=$userid; last_update is a session variable stating when the last time that updates were performed. $userid is the id from the session variable on the serverside. Also I don't know how to easily, do something similar to that since i want to have the join in it from the chat_private table but i also don't want to have to deal with all of the ids from it since it only holds the target_id/message_id and thus would be rather pointless.

What's the easiest way to 开发者_JS百科work with the data as it is? If it's impossible to work with, then i'll just move the private chat and other subsequent ones into their own table and adjust the data sizes accordingly.

You want something like

SELECT chat.message, chat.timesent FROM chat LEFT JOIN chat_private ON chat.id=chat_private.message_id WHERE chat.timesent>='$timestamp' AND chat_private.target_id=$target_id