Need help with JOIN statement (MySQL)

[eluser]IamPrototype[/eluser]
For my news system I need to display the given news and its categorie so I guess I'd have to use JOIN. I've looked through some code from different CI apps and this is what I've come up with so far.

Code:

$this->db->select('news.*, categories.title AS category_title, categories.slug AS category_slug');
$this->db->join('categories', 'news.category_id' = categories.id', 'left');
$query = $this->db->get('news');

I know what the code is doing, except for one thing, the third parameter in my join (left). What is the code doing when it's left? I know the options can be left, right, outer, inner, left outer, and right outer. But what it does.. I couldn't find it anywhere? Could anybody be nice and explain for me, please?

BTW! When I've to show the category data from my get, I'd just go like I always do? Like this:

Code:

<?php foreach($news as $single) : ?>
<?php anchor('controller/method/slug', $single->category_slug);
<?php endforeach; ?>

Right? Sorry, just learning how to join atm, because I haven't done it before and sorry for my english, it isn't my native language (self taught )

[eluser]Wuushu[/eluser]
The join joins in the requested fields from the categories table, and puts them next to on the matching rows (if found = LEFT) of the news table result where news.category_id = categories.id..


If no categories.id are found to match news.category_id then no category is joined onto the row result (NULL.. NULL.. NULL..). If you would have left out the "LEFT", then if no category existed to match the news.category_id existed, then the news-row would have been excluded.

I hope my flimsy explanation helps somewhat.

[eluser]IamPrototype[/eluser]
Aha, thanks!

* With LEFT join -> If no categorie, the categorie WON'T be returned (because it don't exists), but the news will be?
* Without LEFT join -> If no categorie, the news will be excluded and nothing will be returned

So the third parameter are just the way to organize the rows.. from left to right etc? I think I got it.... well at least almost. It helped me a lot.

[eluser]Wuushu[/eluser]
Basically yes, LEFT join on category table basically says it's optional, if not exist match for this news-row, then just skip it but still include the news-row..

[eluser]Dam1an[/eluser]
Also, one other thing

Code:

// instead of
$this->db->join('categories', 'news.category_id' = categories.id', 'left');
// it should be
$this->db->join('categories', 'news.category_id = categories.id', 'left');

the equals condition should all be a single string

[eluser]IamPrototype[/eluser]
Thanks, Dam1an.

[eluser]TheFuzzy0ne[/eluser]
I'd suggest you do yourself a favour and grab yourself an IDE which does some syntax checking.

[eluser]IamPrototype[/eluser]
Actually I do have an IDE with syntax, debugging and all that stuff, but I didn't see that - maybe because it's my first time playing with joins, oh well, I've learned a lot now.