Multiple Table help

[eluser]Nouman6[/eluser]
I'm at my first area to finally retrieve data using a multi-database design, and I’m a bit lost

for example at the dashboard, the user will now see a list of Groups they are apart of (list of the group name’s) now I know how to do this with a single table, but now I have to basically: use user_id from session => get list of rows with that user_id from group_user, THEN use the group_id’s from these rows to retrieve a list of group names from the group table.

but I have no idea how to get started on the controller x]

Code:

group
   id           (int, unsigned, auto-increment, etc)
   name

Code:

user
   id           (int, unsigned, auto-increment, etc)
   name
   email
   pass

Code:

group_user
   id            
   group_id    
   user_id

Also secondly, just to double check. I currently have two models, a user_model and a group_model. Is it recommended to have only one model? past the login point, the user_model won't be called since data is already store in session for the user, therefor only the group_model will be used to create/delete groups as well as handle their inner functions (tasks, comments)

thanks!

[eluser]n0xie[/eluser]
You should read up a bit about JOIN's:
http://www.khankennels.com/blog/index.ph...ing-joins/
http://www.codinghorror.com/blog/2007/10...joins.html
http://en.wikipedia.org/wiki/Join_(SQL)

[eluser]Nouman6[/eluser]
I'm trying to use these tutorials, but I can't wrap my head around using three tables.

in order to get the list of GROUPS the USER is in, I need to query the GROUP_USER table to return a list of GROUP_ID's for the current USER (who's id is stored in session) and then query the GROUPS table to get the info for the group, like it's name.

and I can't seem to understand how to modify the codeigniter joins example

Code:

$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');

$query = $this->db->get();

[eluser]danmontgomery[/eluser]

Code:

$this->db
  ->select('group.name')
  ->join('group', 'group.id = group_user.group_id', 'left')
  ->where('group_user.user_id', $user_id)
  ->get('group_user');

You don't need to join 3 tables, because the only information you would need from the user table is the id, which you already have, and which resides in group_user (which also contains the group id's you need). So you just need to select on group_user, and left join any group records.