Using joins and if statements

Question

I'm trying to query two tables in an incident management system - one holds incident details, the other holds references to third party companies, so am trying to get incident details, and a reference from a distinct third party, if the reference exists. If it doesn't exist I want a blank row returned. But I really don't know the required syntax.

The code I'm using to test is very basic:

SELECT i.incident_number,
    r.reference
FROM incidents i
INNER JOIN references r
    ON i.incident_number = r.incident_number

There is a column in the references table that contains supplier details so I want to say that if the supplier column in references contains 'supplier 2', then return the contents of the reference column, if there is no reference to that supplier, return a blank row.

Thanks in advance.

Answer 1

I think you are looking for a left join:

SELECT i.incident_number, r.reference
FROM incidents.i LEFT JOIN 
     references r
     ON r.incident_number = i.incident_number AND
        r.supplier = 'Supplier 2';