Codeigniter: Join between three tables

I’m trying to do a Join between three tables, in which in one table could not be informations to do the join.

This my tables:

Meetings

• id
• docent_id
• motivation

Persons

• id
• name
• surname

Companions

• id
• meeting_id
• name
• surname

Now I want to recover the information where Meetings.docent_id = Persons.id (and this is ok), I want also recover information where Meetings.id = Companions.meeting_id, and if I add this condition I receive an error.

The problem is that last condition is not always present, because the user can decide if he wants companion or not, (So in case it doesn’t want it they aren’t written to the Companions table) so it could be that some Meetings.id doesn’t have a correspondence in Companions.meeting_id.

I have tried a code like:

 $query = $this->db->select('Meetings.*, Persons.name, Persons.surname, Companions.*')
            ->from('Meetings')
            ->join('Companions', 'Meetings.id = Companions.meeting_id', 'LEFT')
            ->join('Persons', 'Meetings.docent_id = Persons.id', 'INNER')
            ->where('delete_date is null')
           //....

And it returns me a 500 errors, I think because some meetings don’t have Companions. How can I do to obtain all meetings and if there are companions show them?

Thank you.

Answer

$query = $this->db->select('Meetings.*, Persons.name, Persons.surname, Companions.*')
        ->from('Meetings')
        ->join('Companions', 'Meetings.id = Companions.meeting_id', 'LEFT')
        ->join('Persons', 'Meetings.docent_id = Persons.id', 'INNER')
        ->where('Persons.delete_date is null')

be sure you have fully qualified column name for the where (could be you have the same column name in two table joined)