[Solved] Active record join problem.

[eluser]Sceneshift[/eluser]
Hi guys,

I'm trying to join on a table twice, the same field each time.

Code:

$this->db->select('user_users.username as author, user_users.username as last_msg_by')
->join('user_users', 'user_users.id = forums_threads.ID_MEMBER_STARTED', "left")
->join('user_users', 'user_users.id = forums_threads.ID_MEMBER_UPDATED', "left")    
//->join('forums_posts', 'forums_posts.topic_id = forums_threads.ID_FIRST_MSG', "left")
//->where('forums_threads.board', $forum_id)
//->orderby('forums_threads.id', 'desc');

Apparently this query won't work, because the user_users table is not unique, or something. Is it possible to join on the same field twice in one query>

[eluser]Dagobert Renouf[/eluser]
theorically you can join the same field numerous times.

Did you try doing it in pure SQL to see if it was just an Active record limitation ?

[eluser]Sceneshift[/eluser]
Hmm I didn't think of that, but I just tried it and it doesn't seem to work either.

Sorry I guess this isn't really a CI problem.

[eluser]Doosje[/eluser]

Code:

$this->db->select('user.username as author, user.username as last_msg_by')
->join('user_users as user', 'user.id = forums_threads.ID_MEMBER_STARTED', "left")
->join('user_users as users', 'users.id = forums_threads.ID_MEMBER_UPDATED', "left")

try this ..

[eluser]Sceneshift[/eluser]

Code:

$this->db->select('user.username as author, users.username as last_msg_by')
->join('user_users as user', 'user.id = forums_threads.ID_MEMBER_STARTED', "left")
->join('user_users as users', 'users.id = forums_threads.ID_MEMBER_UPDATED', "left")

Worked! (needed to add the 's' in the select statement)

Thank you very much, sorry that it wasn't really CI related.

[eluser]Doosje[/eluser]
we're here to help each other ..aren't we?
I thought i saw the flaw...

have fun

[eluser]Dagobert Renouf[/eluser]
I love to help each other even when I have wrong solutions ahah (that's most of the time).

good job Doosje.