Page 1 of 1

if/then INNER JOIN

Posted: Tue Oct 27, 2009 8:49 am
by psurrena
Hello,

The query below returns everything requested but I would like to modify it so that if there is no project_id (and therefore, no project.name), it will still pull that record.

Any help would be appreciated. Thanks.

Code: Select all

function awardsList($year)
{
    $query = $this->db->query("
        SELECT award_detail.award, award_detail.organization, award.year, project.name
        FROM award_detail
        INNER JOIN award ON award.id = award_detail.award_id
        INNER JOIN project ON project.id = award.project_id
        WHERE award.year = $year
        ORDER BY award_detail.id DESC
    ");
    
    return $query->result();
}

Re: if/then INNER JOIN

Posted: Tue Oct 27, 2009 9:11 am
by Eran
Change the join to the 'project' table to a left-join

Re: if/then INNER JOIN

Posted: Tue Oct 27, 2009 9:19 am
by psurrena
Great, thanks. So a left-join is optional?

Re: if/then INNER JOIN

Posted: Tue Oct 27, 2009 10:18 am
by Eran
A left-join will always bring the row from the left table in the join (the table you are joining to) and will optionally fill the values from the table to the right with NULL if no matching row was found.