Is this a LEFT join by default ?

[eluser]cPage[/eluser]
It's funny how this example could not be more wrong.
It looks like blogs table have primary named id and the comments have a field named id too.

What is the link ? blogs.id = comments.id ? So the id field from comments is the primary key in blogs table. What if you have more than 1 foreign key ?

I do not work like that , it can be to messy.

For me , a real referencial integrity relation between 2 tables should be :

blogs.id_blog

comments.id_comment
comments.blog_id (foreigh key)

Code:

$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.blog_id = blogs.id_blog');
$query = $this->db->get();

// Produces:
// SELECT * FROM blogs
// JOIN comments ON comments.blog_id = blogs.id_blog

comments could be something else than a comment that come from a blog. It could be a comment from lets say table appointments :

Code:

$this->db->select('*');
$this->db->from('appointments');
$this->db->join('comments', 'comments.appointment_id = appointments.id_appointement');
$query = $this->db->get();

// Produces:
// SELECT * FROM appointments
// JOIN comments ON comments.appointment_id = appointments.id_appointement

Unless you want many comments tables. comments_blog, comments_appointements, comments_cake, comments_car, comments_home. etc.