Welcome Guest, Not a member yet? Register   Sign In
Need help with JOIN statement (MySQL)

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.

$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? Smile

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

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

Right? Smile 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 Smile)

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. Smile

Aha, thanks! Smile

* 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. Tongue It helped me a lot.

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..

Also, one other thing
// 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

Thanks, Dam1an. Smile

I'd suggest you do yourself a favour and grab yourself an IDE which does some syntax checking. Smile

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. Smile

Theme © iAndrew 2016 - Forum software by © MyBB