homepage Welcome to WebmasterWorld Guest from 54.211.201.65
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Adding a Second Table into Query
MySQL
cussbee




msg:4048704
 11:24 pm on Dec 23, 2009 (gmt 0)

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

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. Any ideas? THANKS! JC

 

whoisgregg




msg:4054065
 11:54 pm on Jan 4, 2010 (gmt 0)

Welcome to WebmasterWorld, cussbee!

Maybe something like this would do the trick?

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

Kind of hard to tell because I'm not really sure what's going on with the current temporary table and inner join. But, hopefully this will point you in a direction if you haven't already sorted this out. :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved