Self join possible using Active Record?

[eluser]CARP[/eluser]
Hi...
I'm building some sort of catalog.
I have a category table, which has

id - autoinc
name - varchar(100)
desc - tinytext
fk_cat - int(10) foreign key that points to category.id (self reference foreign key)

Of course, I can't execute the following code..

Code:

$this->db->select('*');
$this->db->from('category');
$this->db->join('category', 'category.id = category.fk_id', 'left');
$this->db->order_by('category.name', 'asc');
$query = $this->db->get();

becase I'm getting the following error

Error Number: 1066
Not unique table/alias: 'category'

It seems there's no way to give alias to each table in Active Record calls, right? Any workaround?
Thanks a lot in advance,

[eluser]Armchair Samurai[/eluser]
Definitely possible - just alias the tables

Code:

$this->db->join('category c2', 'c.id = c2.fk_id', 'left');
$this->db->order_by('c.name', 'asc');
$query = $this->db->get('category c');

[eluser]CARP[/eluser]
Very quick reply... working fine, but which kind of join will ignore the rows which have fk_category == null ?

[eluser]Chad Fulton[/eluser]
I think that you might in fact want:

Code:

$this->db->join('category c2', 'c2.fk_id = c.id', 'left');
$this->db->order_by('c.name', 'asc');
$query = $this->db->get('category c');

You want to join where the c2.fk_id = c.id, not where c.id = c2.fk_id

[eluser]CARP[/eluser]
Yes! you're right... it worked perfect!

PS: Another question (if possible)... how do you select (using Active Record) records which its fk_category are = NULL (I want to select main categories, that doesn't have child categories)

Thanks again

Edit: found it...
$this->db->where("fk_category", NULL);