Joining 3 tables

[eluser]Bramme[/eluser]
Hello everybody

I'm having problems with wrapping my head around a join query.

I have 3 tables: users, stories (with a user_id field to identify the author of the story) and subscriptions (with a story_id and users_id field, to show which users are subscribed to which stories).

I want to pull up all the stories (with author user name) for a specific user. At the moment, I use the following query, but it's throwing errors:

Code:

$this->db->select('sd_stories.id, sd_stories.status, sd_stories.title, sd_stories.date_started, sd_stories.last_updated, sd_stories.thumbnail, sd_stories.breakdown, sd_stories.parts, sd_users.username')
->from('sd_stories')
->join('sd_users', 'sd_stories.author_id = sd_users.id')
->join('sd_story_subscriptions', 'sd_stories.id = sd_story_subscriptions.story_id')
->order_by('date_started', 'asc')
->where('sd_story_updates.users_id', $this->auth->get_userid());

This is the error:

Code:

Error Number: 1066

Not unique table/alias: 'sd_users'

SELECT `sd_stories`.`id`, `sd_stories`.`status`, `sd_stories`.`title`, `sd_stories`.`date_started`, `sd_stories`.`last_updated`, `sd_stories`.`thumbnail`, `sd_stories`.`breakdown`, `sd_stories`.`parts`, `sd_users`.`username`, `sd_news`.`id`, `sd_news`.`title`, `sd_news`.`date_posted`, `sd_news`.`last_updated`, `sd_news`.`message`, `sd_users`.`username` FROM (`sd_stories`, `sd_news`) JOIN `sd_users` ON `sd_stories`.`author_id` = `sd_users`.`id` JOIN `sd_story_subscriptions` ON `sd_stories`.`id` = `sd_story_subscriptions`.`story_id` JOIN `sd_users` ON `sd_news`.`user_id` = `sd_users`.`id` WHERE `sd_story_updates`.`users_id` = '10' ORDER BY `date_started` asc, `date_posted` desc

[eluser]Bramme[/eluser]
Hmm, never mind. Seems I got it working with

Code:

$this->db->from('sd_stories')
         ->select('sd_stories.id, sd_stories.title, sd_stories.date_started, sd_stories.last_updated, sd_stories.parts, sd_stories.status, sd_users.username')
         ->join('sd_users', 'sd_users.id = sd_stories.author_id')
         ->join('sd_story_subscriptions', 'sd_stories.id = sd_story_subscriptions.story_id')
         ->where('sd_story_subscriptions.users_id', $this->auth->get_userid());
$qryMyStories = $this->db->get();