CodeIgniter Forums
Join Question - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Join Question (/thread-20314.html)



Join Question - El Forum - 07-05-2009

[eluser]derekmichaeljohnson[/eluser]
I have 3 tables: users, users2cats, and cats. I'd like to be able to list all the categories, as well as the number of users in each. Can I do this with a single query?

My code so far:

Code:
$this->db->select('users.*, users2cats.cat_id, cats.name AS cat');
$this->db->from('users');
$this->db->join('users2cats', 'users.id = users2cats.user_id');
$this->db->join('cats', 'users2cats.cat_id = cats.id');



Join Question - El Forum - 07-05-2009

[eluser]garymardell[/eluser]
Code:
$this->db->select('users.*, users2cats.cat_id, cats.name AS cat, COUNT(users.id) as user_count');
$this->db->from('users');
$this->db->join('users2cats', 'users.id = users2cats.user_id');
$this->db->join('cats', 'users2cats.cat_id = cats.id');
$this->db->group_by("cats.id");

May work.


Join Question - El Forum - 07-05-2009

[eluser]derekmichaeljohnson[/eluser]
Works great!

Only problem is it doesn't show categories that don't have any users assigned to it yet. How can I get it to display empty categories with a zero?

EDIT:

Answered my own question... just made it a LEFT join. I also took the "users" table out of the query, as I don't really need user information for this particular usage. Here's my final code:

Code:
$this->db->select('cats.*, users_cats.cat_id, COUNT(users_cats.user_id) as user_count');
$this->db->from('cats');
$this->db->join('users_cats', 'users_cats.cat_id = cats.id','left');
$this->db->group_by("cats.id");
$data['cats'] = $this->db->get();



Join Question - El Forum - 07-05-2009

[eluser]garymardell[/eluser]
All sorted then?
Glad i could help.