Joining 3 tables - Printable Version

Joining 3 tables - Printable Version

+- 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: Joining 3 tables (/showthread.php?tid=27740)

Joining 3 tables - El Forum - 02-19-2010

[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

Joining 3 tables - El Forum - 02-19-2010

[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();