Join Question

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

[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.

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

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