What I'm trying to do in the query below: questions are posted that belong to 1 continent and 1 country. Answers are posted to these questions by a user. In the query below I get all that data and the number of answers.
The issue is that the COUNT(answers.text) returns a number of 1 when it should actually be zero. This is in case there is no answer to a question.
To cut a long story short, I'm not sure how the GROUP_BY should be used and feel this is the problem. Can anyone explain?
$this->db->select('questions.id, users.username, url_title, title, questions.text, questions.date_posted, continents.full_name as continent, countries.full_name as country');
$this->db->select("COUNT('answers.text') as nbr_answers");
$this->db->join('continents', 'questions.loc_continent = continents.id');
$this->db->join('countries', 'questions.loc_country = countries.id');
$this->db->join('answers', 'answers.q_fid = questions.id', 'left');
$this->db->join('users', 'users.id = answers.user_id_fk', 'left');
$this->db->order_by('questions.id','desc');
$this->db->group_by('url_title');