I have tables "skill_rating" and "skill_description". descriptionId column of skill_description is foreign key in skill_rating. I want to count the number of staffId for each description with rating 2.
$query1=$this->db->select('skill_description.description, count(staffId) as experienced')
->from('skill_rating')
->join('skill_description','skill_rating.descriptionId = skill_description.descriptionId')
->where_in ('skill_description.descriptionId',$id)
->where ('skill_rating.rating','2')
->group_by("skill_description.description")
->order_by("skill_description.description asc")
->get();
This only returns me results with description having non zero count meaning it does not gives count 0 for description with zero counts. i have tried coalesce but no difference. i want result with 0 for descriptions that do not have rating 2. what should i do?