Query problem, have to work with joins

[eluser]cmh24[/eluser]
I have to tables filled with data (see below).

id | org_id | com_id
1 6 2
2 6 3
3 7 2
4 7 4


id | company
1 Ford
2 GM
3 Chrysler
4 Toyota

And now I show you what I want to get. You have to think from the perspective of organizer 6(org_id). (com_id =company id)

id | company | is_registered
1 Ford NO
2 GM YES
3 Chrysler YES
4 Toyota NO

Code:

$query = $this->db->select('*')
                          ->from('companies')
                          ->join('org_com','companies.id = org_com.com_id')
                          ->where('org_id', $this->session->userdata('user_id'))
                          ->get();

But this one only gives me back the ones that are registered and not not the other ones.

Thanks for your help!
Max

[eluser]bgreene[/eluser]
left join

[eluser]cmh24[/eluser]
I am still struggling.
Tried to read some instructions on "left joins", also searched the forum but it is still not working :-(

[eluser]cmh24[/eluser]
After a long search I found a solution.

Code:

$sql = "SELECT * FROM companies
                LEFT JOIN
                (SELECT * FROM org_com WHERE(org_com.org_id=6)) AS t
                ON (companies.id = t.com_id)";
        
$query = $this->db->query($sql);

Now, I am wondering if it would be possible in Active Record.


Max

[eluser]InsiteFX[/eluser]
Try this, not tested:

Code:

$this->db->join('org_com','companies.id = org_com.com_id', 'left')
     ->where('org_id', $this->session->userdata('user_id'))
     ->get('companies');

InsiteFX