StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSingle query for all records having no key in a join table and those having a matching key
text
Body
copied!<p>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.</p> <p>I can get the first from this query:</p> <pre><code>SELECT * FROM `trips` WHERE (((NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id)) </code></pre> <p>But to get the second, I need to add this bit as well:</p> <pre><code>INNER JOIN `residencies` ON (`trips`.`id` = `residencies`.`trip_id`) </code></pre> <p>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.</p> <p>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.</p> <p>Currently, I have the first requirement as a named scope:</p> <pre><code>Trip.named_scope :residencies_empty, :conditions => ['NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id)'] </code></pre> <p>The second requirement is available through searchlogic:</p> <pre><code>Trip.residences_id_equals(id) </code></pre> <p>The ideal solution would be a searchlogic scope that looks like this:</p> <pre><code>Trip.residencies_null_or_residencies_id_equals(id) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload