if/then INNER JOIN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

if/then INNER JOIN

Post 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();
}
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: if/then INNER JOIN

Post by Eran »

Change the join to the 'project' table to a left-join
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Re: if/then INNER JOIN

Post by psurrena »

Great, thanks. So a left-join is optional?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: if/then INNER JOIN

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