Victory over a big wall

Progress on Lemon-filling has been going well, though I hit a big wall a couple of days ago. I was having problems getting the exact results I wanted with my query.

The problem was, when requesting a pages terms, I wanted all the terms and their definitions, like this if asking for locale_id=1:

+---------------+------------------------------------------+
| terms.value   | rosetta.value                            |
+---------------+------------------------------------------+
| lname         | Username:                                |
| lpass         | Password:                                |
| welcome_blurb | Welcome to appland! Please log in below. |
+---------------+------------------------------------------+

If I requested a locale that existed, but didn't have some or any of the terms defined yet, I wanted this:

+---------------+---------------+
| terms.value   | rosetta.value |
+---------------+---------------+
| lname         |               |
| lpass         |               |
| welcome_blurb |               |
+---------------+---------------+

But I was just getting an empty result set. Spent a while agonizing over it, eventually took the time to write up my problem and posted it on dba.stackexchange.com. Within minutes help arrived!

It pointed me in the right direction, but the solution offered:

SET @given_locale_id = 1;
SELECT terms_value,MAX(rosetta_value) rosetta_value
FROM
(
    SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    FROM terms T LEFT JOIN rosetta R
    ON T.terms_id=R.terms_id
    UNION
    SELECT T.value,'',@given_locale_id FROM terms T
) A
WHERE
    locale_id = @given_locale_id
GROUP By terms_value;

Only worked when asking for all terms. I also need a query where I give it the page key, and it only gives be back the terms for that page. Tinkered a lot, and eventually came up with a solution:

SET @given_locale_id = 1;
SELECT terms_value, MAX( rosetta_value ) rosetta_value
FROM (
 
    SELECT T.value terms_value, T.terms_id, R.value rosetta_value, R.locale_id
    FROM terms T
    LEFT JOIN rosetta R ON T.terms_id = R.terms_id
    UNION
    SELECT T.value, T.terms_id, '', @given_locale_id
    FROM terms T        
 
)A
WHERE locale_id =@given_locale_id AND terms_id IN (SELECT terms_id FROM page_group INNER JOIN page ON page.page_id=page_group.page_id WHERE page.value='sign_in')
GROUP BY terms_value;

It is one of the most complex queries I've had to write. I will probably need to optimize some too, but I'm waiting until I know for sure that my schema design will work before putting in foreign key constraints, which should help.