Querying from Multiple Tables

[cussbee]

Here's my prob, finally got the first part working but I need to add in another table to this query. The query below is on the "messages" table that stores all the different messages in a threaded system and grabs the latest message in a thread.

 

SELECT * FROM (SELECT thread, MAX(id) AS id FROM messages GROUP BY thread) t1 INNER JOIN messages t2 ON t2.thread=t1.thread AND t1.id=t2.id WHERE tome = '$userid' AND INDELETE !='y' ORDER BY t1.id DESC

 

My problem is, I need to pull out the fields "firstname", "lastname" and "image1" of the user sending the message to the person from the table "users" where the field "fromme" in the messages table is = to the "id" field in the users table. How would I got about changing up the query to make this go? Thanks!

[premiso]

I am not great at knowing which type of join to use, but this should work:

 

SELECT * FROM 
(SELECT thread, MAX(id) AS id FROM messages GROUP BY thread) t1 
INNER JOIN messages t2 ON t2.thread=t1.thread AND t1.id=t2.id 
JOIN users t3 ON t2.fromme = t3.id
WHERE tome = '$userid' AND INDELETE !='y' ORDER BY t1.id DESC

 

See if that does what you want it to.