Strange query, need help

[eluser]CodeIgniterNoob[/eluser]
Hello,

I need to write a weird query.
I have two tables, "categories", and "sub_categories",

"categories" fields are
category_id category_name category_level category_status parent_id parent_name category_sortorder

and "sub_categories" fields are
sub_category_id sub_category_name sub_category_status sub_category_sortorder category_id

I need to pull out all the sub's based on the sub_category_name(this is what Im passing to the query). So I need to pull out all the other sub_categories which belong to the main category by category_id.
Can SQL even do that?

Code:

function returnSubCategories($catname=NULL)
    {        
        $this->db->select('*');
        $this->db->from('sub_categories','categories');
        $this->db->where('sub_category_name', $catname);    
        $this->db->join('categories', 'categories.category_id = sub_categories.category_id');
        $Q = $this->db->get();
        
        if ($Q->num_rows() > 0)
        {
            return $Q->result_array();
        }
        
        $Q->free_result();
}

[eluser]gullah[/eluser]
I'm not sure how well this works with CI as I've never tried it but you could try joining sub_categories with itself where category_id = category_id. You might have to make it a left join in case there are no other sub_cateogries with that category_id. Or else you could get the results back from the first query and do another query where category_id = $results->cat_id and $sub_cat_id != $results->sub_cat_id.

Good luck.

[eluser]crumpet[/eluser]
I think you want to join the categories back to the subcategories
so

Code:

$query = $this->db
   ->where('a.sub_category_name', $sub_category)
   ->join('categories AS b', 'a.category_id = b.category_id', 'inner') //inner b/c it has to have a parent
   ->join('sub_categories AS c', 'b.category_id = c.category_id', 'inner') //this is our result
   // may want to ->group_by('c.category_id') here for more complex selects...
   ->select('c.*')
   ->get('categories AS a');

[eluser]gullah[/eluser]
[quote author="crumpet" date="1230418617"]I think you want to join the categories back to the subcategories
so

Code:

$query = $this->db
   ->where('a.sub_category_name', $sub_category)
   ->join('categories AS b', 'a.category_id = b.category_id', 'inner') //inner b/c it has to have a parent
   ->join('sub_categories AS c', 'b.category_id = c.category_id', 'inner') //this is our result
   // may want to ->group_by('c.category_id') here for more complex selects...
   ->select('c.*')
   ->get('categories AS a');

[/quote]

problem with this is you are trying to select the sub_category_name from the categories table which doesn't exist.

[eluser]CodeIgniterNoob[/eluser]
thanks guys, i will try this out.

[eluser]crumpet[/eluser]
sorry typo
i meant ->get('sub_categories AS a')

[eluser]CodeIgniterNoob[/eluser]
Its working perfectly. I knew I had to do an inner join. Just thought I would try out the forum and bang!! instant help and a solution. Thanks alot guys.