Refactor SQL (workaround RIGHT OUTER JOIN)

Question

Hey Stackers,

Since SQLite does not support RIGHT OUTER JOINS I pose the following challenge (read: invitation to do my work for me):

Refactor this query so it no longer utilises SQLite-unsupported constructs like RIGHT/FULL OUTER JOINs.

SELECT     strings.*, translations.text
FROM         translations INNER JOIN
                      language ON translations.language_id = language.id RIGHT OUTER JOIN
                      strings ON translations.string_id = strings.id
WHERE     (language.handle = 'english')

I sense it can be achieved with subqueries or by pivoting the tables and performing a LEFT OUTER JOIN but my attempts have failed; my SQL's not what it used to be.

Here's a query builder outline showing the applicable schema: http://dl.getdropbox.com/u/264612/sql-refactor.PNG

First to crack it gets an e-hug from dekz

Answer 1

The following is untested.

select strings.*, translations.text
from strings left outer join translations
     on translations.string_id = strings.id
           and translations.language_id = (select id
                                           from language
                                           where language.handle = 'english')

I think this will give you all strings with the matching translation text where a suitable translation exists in English. Is that what you are trying to get?

Answer 2

Intriguing that SQLite allows LEFT OUTER JOINs but not RIGHT OUTER JOINs. Well, since it does allow LEFT OUTER JOINs, you're right, you can just rearrange the join order:

SELECT     strings.*, translations.text
FROM strings LEFT OUTER JOIN (
    translations INNER JOIN language ON translations.language_id = language.id
) tr ON tr.string_id = strings.id
WHERE     (language.handle = 'english')

[EDIT: Applied Blorgbeard's suggestion of naming the joined table to get the query to parse -- hope it works now!]