Correct syntax for getting information from two tables

[Chris Hick]

I know this is possible and I've done it quite often in the past. But I haven't used MySQL in months(been doing some C++ work. Not that it has anything to do with C++.) Anyhow, what I have is two table Posts and Comments and I'm trying to get all the posts and then count the number of comments those posts have. I do not need to get any information from the comments table other than counting how many comments there are to a single post. I want to do this with mysql only.

[abduraooft]

Please post the relevant column names of your tables.

[Chris Hick]

Sorry, I am just now getting on. I have figured out how to do it and return the result that I like. The only issue I have is that it always returns information even when there is no data to be returned. Btw, I am using codeigniter framework at the moment. Here is what I used:

$this->db->select("posts.*, COUNT(comments.id) AS numberOfComments", FALSE); 
$this->db->where('posts.type_id',$type);
$this->db->from('posts');
$this->db->join('comments', 'comments.post_id = posts.id', 'left');