Need Help with double LEFT JOIN SQL Query

[ZomgStuff]

So currently this is my query.

PHP Code:

                $rating_load = $db->query_read("
                SELECT  p.postid, p.threadid, p.title, p.pagetext, r.*
                FROM " . TABLE_PREFIX . "erate AS r
                LEFT JOIN ".TABLE_PREFIX."post AS p ON(p.postid = r.pid)
                WHERE r.rating = '".$rate_id."' AND r.uid = '".$user_id."' AND p.userid = '".$thisUser."'
                ORDER BY p.postid DESC
                "); 


Only problem is that I actually need thread.title as opposed to post.title, but I would sitll like to keep post.pagetext.

Now how do I do a double LEFT JOIN to link all there of these into a single array, I need to be able to use the following variables to output them into HTML.

thread.title, thread.threadid
post.pagetext, post.postid

I've made a little graphic representation of the 3 tables that I have.


Thank you in advance.

Edit: This has been resolved, thanks!

[Boofo]

What is it exactly you are wanting to pull with the query? And why not just LEFT JOIN the thread table?

[ZomgStuff]

Because I still need the post.pagetext (which is the actual post content).

Basically I need to do a search by a "rating" type and "userid" , and be able to get the thread id, post id, post content, and thread title.

Would it just be simpler to do two separate LEFT JOIN's, one linking thread and the other post?

[Boofo]

That's the way I would go but I'm far from an expert on queries.

[ZomgStuff]

How would I be able to get the post conent. Would it be effcient to do two seperate LEFT JOIN's and then implode the two arrays?


--------------- Added [DATE]1213156014[/DATE] at [TIME]1213156014[/TIME] ---------------

I actually got the double LEFT JOIN query working, it was much easier that I thought.

Thanks for trying to help.

Finished product

[Boofo]

How does the query look now? I'm curious to see what you came up with.

[Opserty]

[sql]
SELECT p.postid, p.threadid, p.title, p.pagetext, r.*, t.threadtitle
FROM " . TABLE_PREFIX . "erate AS r
LEFT JOIN ".TABLE_PREFIX."post AS p ON(p.postid = r.pid)
LEFT JOIN ".TABLE_PREFIX."thread AS t ON(p.threadid = t.threadid)
WHERE r.rating = '".$rate_id."' AND r.uid = '".$user_id."' AND p.userid = '".$thisUser."'
ORDER BY p.postid DESC
[/sql]
I think that should work, not tested though. Might be a more efficient way of doing it but thats the way I would have done it

[ZomgStuff]

Originally Posted by Boofo

How does the query look now? I'm curious to see what you came up with.

[SQL] SELECT p.postid, p.threadid, p.pagetext, t.threadid, t.title, r.*
FROM " . TABLE_PREFIX . "erate AS r
LEFT JOIN ".TABLE_PREFIX."post AS p ON(p.postid = r.pid)
LEFT JOIN ".TABLE_PREFIX."thread as t ON(t.threadid = p.threadid)
WHERE r.rating = '".$rate_id."' AND r.uid = '".$user_id."' AND p.userid = '".$thisUser."'
ORDER BY p.postid DESC
[/SQL]