+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Complex join statement (/showthread.php?tid=9002)
Complex join statement - El Forum - 06-08-2008
[eluser]crumpet[/eluser]
I'm programming a private message system for users. The table pmessages stores userID's in the field mRecipient and mAuthor. When I access a message I want to join both those fields to the field userID in the table users so i can display usernames instead of userID's
Is there syntax to do this in one query or do i need to do two...?
basically i want to join two seperate fields in one table to the same field in another table
Complex join statement - El Forum - 06-09-2008
[eluser]tchule[/eluser]
Hello,
I think you have to join the user table twice, one time for the recipient and one time for the author.
Something like this (approximate syntaxe) :
Code:
SELECT *
FROM messages m
JOIN users u1 having (u1.userid = m.authorid)
JOIN users u2 having (u2.userid = m.recipientid)Tchule