Hi,

I'm making a thread/mail system, with the folowing structure

CREATE TABLE mailbox (
thread_id int(11) NOT NULL,
last_date_seen datetime default NULL,
label enum('inbox','trash','burnt') NOT NULL,
user_id int(10) unsigned NOT NULL,
mailbox_id int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (mailbox_id),
KEY label (label)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE messages (
msg text,
date_sent datetime NOT NULL,
thread_id int(11) NOT NULL,
from_id int(11) NOT NULL,
KEY date_sent (date_sent,thread_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE threads (
thread_id int(11) NOT NULL auto_increment,
title varchar(255) default NULL,
PRIMARY KEY (thread_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

so 'threads' holds one records for each thread. 'messages' holds multiple records for each thread, where thread_id would be a foreign key pointing to 'threads'.thread_id. any time someone writes a message in the thread, it is added to the 'messages' table.
lastly, 'mailbox' holds multiple records for each thread according to the number of users that are part of the thread. e.g. if i start a thread between myself and 2 friends such that our user_id's are 1,2,3; there will be 1 thread_id, and 3 records in 'mailbox' with that thread_id. reason for that is so each user has an individual date_last_seen so i can calculate which messages in each thread are unread - thats where my question comes in.

how do i perform in as optimised a way as possible (obviously) the query to get all the information to display the user's inbox?

this grabs all the info (i think):

SELECT threads.thread_id, threads.title FROM threads RIGHT JOIN mailbox ON threads.thread_id=mailbox.thread_id WHERE mailbox.label = 'inbox' AND mailbox.user_id = 1

but im looking to incorporate in the same query a way to also get unread=[0|1]
i looked into functions and thought something like this might be handy:

DELIMITER/

CREATE FUNCTION latest_message(t_id) RETURNS DATETIME
BEGIN
DECLARE temp DATETIME;
SELECT INTO temp MAX(date_sent) FROM messages WHERE thread_id = t_id;
RETURN temp;
END /

DELIMITER ;

but i read that selects in functions arent allowed (i would have tested this first-hand on my host, but it crashed a few minutes ago)... im new to DB programming (although v. familiar with procedural/OO) so any help would be great.

thanks!