Single query for all records having no key in a join table and those having a matching key

Question

I have a trip that has many residencies. I need a single query that returns all trips where no residency information has been specified. And all trips that match a specified residency.

I can get the first from this query:

SELECT * FROM `trips` WHERE (((NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id))

But to get the second, I need to add this bit as well:

INNER JOIN `residencies` ON (`trips`.`id` = `residencies`.`trip_id`)

Adding the join before the WHERE clause is asking for results that have a residency ID and no residency IDs. That obviously returns nothing. So how can I write this to get the full result set in one query? Stored procedures aren't allowed on this.

I'm using Rails, so it's a bonus (but definitely not required) if the answer is Rails-specific. And a huge bonus if someone can show how this can be done with the searchlogic plugin.

Currently, I have the first requirement as a named scope:

Trip.named_scope :residencies_empty, :conditions => ['NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id)']

The second requirement is available through searchlogic:

Trip.residences_id_equals(id)

The ideal solution would be a searchlogic scope that looks like this:

Trip.residencies_null_or_residencies_id_equals(id)

Answer 1

TRY:

SELECT * FROM `trips`
    LEFT JOIN  residencies ON trips.id = residencies.trip_id

You will get data in all of the columns from trips, but data will only be populated in columns from residencies where a row existed, if no residencies row existed those columns will be null.

Answer 2

Have you tried using a UNION to combine the results of the two queries?

Answer 3

I suggest using another "EXIST" for the trips with the specific residency:

SELECT * FROM `trips` WHERE 
  (NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id))
  OR
  (EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id 
           AND other_criteria_for_specific_residency)
  )

This seems to be the most readable solution, but if performance is important, you should check EXPAIN to see how this is optimized (as with most complex queries in MySql).