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)
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).