[El Forum]

[eluser]Wonder Woman[/eluser]
Hi there,

I have a news article database table which I have stored the id of a user in it twice, once for the author and secondly for who modified the article. So I need to do a multiple join but its not working properly.

My code is:

Code:

$this->db->select('*, news.id as news_id, news.created as created_on');
$this->db->from('news');
$this->db->join('users', 'users.id = news.author and users.id = news.modified_by', 'left');
$this->db->get();

If you could help me, that would be great. Thanks!

[El Forum]

[eluser]aquary[/eluser]
The joining condition is not good (for what the query is for). It'd works only if the news author and the modifier is the same person.

You have to separate the join into two parts.

Code:

$this->db->join('users', 'users.id = news.author', 'left');
$this->db->join('users modified_user', 'modified_user.id=news.modified_by', 'left')

Optionally, you may also have to change the select to choose the name of each person manually.

Code:

$this->db->select('news.*, news.id as news_id, news.created as created_on, users.name as author_name, modifier_users.name as modifier_name');

[El Forum]

[eluser]Wonder Woman[/eluser]
Thanks for your reply but they're not in separate tables, I have author and modified_by in the news table, which stores the id of a user which I want to retrieve from the users table.

For example, an article might have been created by author whose id is 1 but later edited by a user whose id is 2...how do I amend my query to the db to get these two different users?

[El Forum]

[eluser]aquary[/eluser]
I understand that it's not in separate tables, but my answer was that "you have to join the users table 2 times". I also have that kind of query for my webboard topics & replies, which looks like....

Code:

$this->db->select("topics.*, topic_creater.username as creater_name, topic_updater.username as updater_name")
->join("users topic_creater", "topics.topic_userid=topic_creator.user_id")
->join("users topic_updater", "topics.topic_modified_userid=topic_updater.user_id")
->get("topics");

Please note that I join on the same "users" table 2 times, but aliased it differently for selecting theirs data.

[El Forum]

[eluser]Wonder Woman[/eluser]
Ah I see, sorry I didn't realise, I've not used

Code:

join('users modified_user'...

to change the table name for the query.