+- 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: Join Question (/showthread.php?tid=20314)
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.