Cannot come up with mysql query.

[petroz]

I am at a loss here guys. Please see if you can help me put this query together.

 

I have a table called messages.

 

CREATE TABLE  `db.app`.`messages` (
`id` INT( 32 ) NOT NULL AUTO_INCREMENT ,
`sender` VARCHAR( 64 ) NOT NULL ,
`recipient` VARCHAR( 64 ) NOT NULL ,
`body` TEXT NOT NULL ,
`timestamp` INT( 32 ) NOT NULL ,
PRIMARY KEY (  `id` )
) ENGINE = MYISAM ;

 

And a table called users.

 

 

CREATE TABLE  `db.app`.`users` (
`id` INT( 32 ) NOT NULL AUTO_INCREMENT ,
`nickname` VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY (  `id` )
) ENGINE = MYISAM ;

 

When a message is made, it inserts the ID of the sender into `messages`.`sender`, the ID of the recipient into `messages`.`recipient`, the message body, and UNIX timestamp. This is working fine.

 

My problem lies with getting a list of all the unique conversations. (Like text messages on iPhones).

 

so if we have data like so...

 

messages table

id | sender | recipient | body | timestamp

1  | 1234 | 5678 | testing message | 1290233086

2  | 5678 | 1234 | testing reply | 1290233089

 

users table

id | nickname

1234 | john

5678 | peter

 

I would like to be able to generate query results like so...

results

other_person_id | other_person_nickname | last_message | last_message_timestamp

1234, john, testing reply, 1290233089

 

For the life of me, I cant figure out this query....

 

[revraz]

select a.recipient, b.nickname, a.body, a.timestamp

from messages a

join users b on (a.recipient = b.id)

where a.recipient = 1234

 

I would also change recipient and sender to INT and not varchar, since they are in fact your other IDs.