CodeIgniter join problem

Question

I want to select from 3 tables "Forum_traad" "Forum_kommentare" "users"

I want to collect from all fields in "forum_traad" and "forum_kommentare" and only collect the field "profil_billed" in my users tables and I want to show the picture that fits the user that created the thread, and those who created a respons in the thread.

I have a field in "forum_traad" and "forum_kommentare" thats called "Brugernavn" and it has to match "Brugernavn" in the "users" tables.

Hope you understand what I mean, this is what I made so far:

model file:

 $this->db->select('*,forum_traad.indhold as traad_indhold,
                      forum_kommentare.indhold as kommentare_indhold,
                      forum_traad.brugernavn as traad_brugernavn,
                      forum_traad.id as traad_id, users.profil_billed as billed
                   ');
            $this->db->from('forum_traad');
            $this->db->join('forum_kommentare', 'forum_kommentare.fk_forum_traad = forum_traad.id');
            $this->db->join('users', 'forum_traad.brugernavn = users.profil_billed');
            $this->db->where('forum_traad.id', $id);
            $query = $this->db->get();

my view file:

 if($query) 
 {
 echo $query->overskrift;
 } else {
 echo "der er ikke noget";
 }

Answer 1

one simple piece of advice: when you post your question try to change data (your code) to English ... and describe what some parameters - data means in your program, relations in db tables, fk-s...in that case more people will try to help you and some things for them will look more obvious than if they have to decipher whats going on here...

So I will try to help:

This is your echo sql:

SELECT *, forum_traad.indhold as traad_indhold, 
          forum_kommentare.indhold as kommentare_indhold, 
          forum_traad.brugernavn as traad_brugernavn, 
          forum_traad.id as traad_id, 
          users.profil_billed as billed 
FROM (forum_traad) 
    JOIN forum_kommentare ON forum_kommentare.fk_forum_traad = forum_traad.id 
    JOIN users ON forum_traad.brugernavn = users.profil_billed 
WHERE forum_traad.id = '5' 

Translation from Danish (I suppose) to English will be something like this:

SELECT *, forum_thread.content as thread_content, 
          forum_comments.content as comments_content, 
          forum_thread.user as thread_user, 
          forum_thread.id as thread_id, 
          users.profil_image as image 
FROM (forum_thread) 
    LEFT JOIN forum_comments 
    ON forum_comments.fk_forum_thread = forum_thread.id 
        LEFT JOIN users 
        ON forum_thread.user = users.profil_image 
WHERE forum_thread.id = '5' 

In last join you join users table on image column?.. try instead:

LEFT JOIN users
ON forum_comments.user = users.id

In that case you will get all people who post comments in a thread (including main poster because his post is first?)

I'm not sure if this is the right solution (answer). If its not post your comment here and we will try to help.