CODEWITHSUNDEEP
sqlpostgresql
Arya
Arya

Reputation: 8995

No rows returned if joined table does not return results

I'm using multiple inner joins right now, but one of the tables may contain no results at all.

Below is the query that I'm currently using

SELECT
   property.id,
   full_address,
   street_address,
   street.street,
   city.city as city,
   state.state_code as state_code,
   zipcode.zipcode as zipcode, 
   property_history.date_event AS event_date,
   property_history.event AS event
FROM
    property 
    INNER JOIN street  ON street.id = property.street_id 
    INNER JOIN city    ON city.id = property.city_id
    INNER JOIN state   ON state.id = property.state_id
    INNER JOIN zipcode ON zipcode.id = property.zipcode_id
    INNER JOIN property_history ON property_history.property_id = property.id
WHERE
    full_address = ?

Right now if property_history does not return any rows then I would not get anything. I assume I need to use another type of join?

I still want to get the property.id, full_address, street_address, etc even though the property_history is empty.

Upvotes: 0

Views: 43

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176064

You should use LEFT OUTER JOIN:

LEFT JOIN property_history ON property_history.property_id = property.id

Upvotes: 2

Related Questions