Query for localization app
This took some doing, but I think I got it.

Here is your solution

SET @given_locale_id = 3;
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;


I loaded your sample data with this:

DROP DATABASE IF EXISTS timhaserback;
CREATE DATABASE timhaserback;
use timhaserback
CREATE TABLE locale
(
    locale_id INT NOT NULL AUTO_INCREMENT,
    value VARCHAR(128),
    PRIMARY KEY (locale_id)
);
INSERT INTO locale (`value`) VALUES
('English'),('Deutch'),('French');
CREATE TABLE terms
(
    terms_id INT NOT NULL AUTO_INCREMENT,
    value VARCHAR(128),
    PRIMARY KEY (terms_id)
);
INSERT INTO terms (`value`) VALUES
('lname'),('lpass'),('welcome_blurb');
CREATE TABLE rosetta
(
    rosetta_id INT NOT NULL AUTO_INCREMENT,
    locale_id INT NOT NULL,
    terms_id INT NOT NULL,
    value VARCHAR(128),
    PRIMARY KEY (rosetta_id)
);
INSERT INTO rosetta (locale_id,terms_id,value) VALUES
(1,1,'Username:'),
(2,1,'Benutzername:'),
(1,2,'Password:'),
(2,2,'Passwort:'),
(1,3,'Welcome to appland! Please log in below.'),
(2,3,'Welcome to appland Bitte melden Sie sich unten.');
SELECT * FROM locale;
SELECT * FROM terms;
SELECT * FROM rosetta;


Here is the sample data actually loaded:

mysql&gt; DROP DATABASE IF EXISTS timhaserback;
Query OK, 3 rows affected (0.14 sec)

mysql&gt; CREATE DATABASE timhaserback;
Query OK, 1 row affected (0.00 sec)

mysql&gt; use timhaserback
Database changed
mysql&gt; CREATE TABLE locale
    -&gt; (
    -&gt;     locale_id INT NOT NULL AUTO_INCREMENT,
    -&gt;     value VARCHAR(128),
    -&gt;     PRIMARY KEY (locale_id)
    -&gt; );
Query OK, 0 rows affected (0.08 sec)

mysql&gt; INSERT INTO locale (`value`) VALUES
    -&gt; ('English'),('Deutch'),('French');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql&gt; CREATE TABLE terms
    -&gt; (
    -&gt;     terms_id INT NOT NULL AUTO_INCREMENT,
    -&gt;     value VARCHAR(128),
    -&gt;     PRIMARY KEY (terms_id)
    -&gt; );
Query OK, 0 rows affected (0.09 sec)

mysql&gt; INSERT INTO terms (`value`) VALUES
    -&gt; ('lname'),('lpass'),('welcome_blurb');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql&gt; CREATE TABLE rosetta
    -&gt; (
    -&gt;     rosetta_id INT NOT NULL AUTO_INCREMENT,
    -&gt;     locale_id INT NOT NULL,
    -&gt;     terms_id INT NOT NULL,
    -&gt;     value VARCHAR(128),
    -&gt;     PRIMARY KEY (rosetta_id)
    -&gt; );
Query OK, 0 rows affected (0.10 sec)

mysql&gt; INSERT INTO rosetta (locale_id,terms_id,value) VALUES
    -&gt; (1,1,'Username:'),
    -&gt; (2,1,'Benutzername:'),
    -&gt; (1,2,'Password:'),
    -&gt; (2,2,'Passwort:'),
    -&gt; (1,3,'Welcome to appland! Please log in below.'),
    -&gt; (2,3,'Welcome to appland Bitte melden Sie sich unten.');
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM locale;
+-----------+---------+
| locale_id | value   |
+-----------+---------+
|         1 | English |
|         2 | Deutch  |
|         3 | French  |
+-----------+---------+
3 rows in set (0.00 sec)

mysql&gt; SELECT * FROM terms;
+----------+---------------+
| terms_id | value         |
+----------+---------------+
|        1 | lname         |
|        2 | lpass         |
|        3 | welcome_blurb |
+----------+---------------+
3 rows in set (0.00 sec)

mysql&gt; SELECT * FROM rosetta;
+------------+-----------+----------+-------------------------------------------------+
| rosetta_id | locale_id | terms_id | value                                           |
+------------+-----------+----------+-------------------------------------------------+
|          1 |         1 |        1 | Username:                                       |
|          2 |         2 |        1 | Benutzername:                                   |
|          3 |         1 |        2 | Password:                                       |
|          4 |         2 |        2 | Passwort:                                       |
|          5 |         1 |        3 | Welcome to appland! Please log in below.        |
|          6 |         2 |        3 | Welcome to appland Bitte melden Sie sich unten. |
+------------+-----------+----------+-------------------------------------------------+
6 rows in set (0.00 sec)

mysql&gt;


Let's try it against locale_id 1

mysql&gt; SET @given_locale_id = 1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT terms_value,MAX(rosetta_value) rosetta_value
    -&gt; FROM
    -&gt; (
    -&gt;     SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    -&gt;     FROM terms T LEFT JOIN rosetta R
    -&gt;     ON T.terms_id=R.terms_id
    -&gt;     UNION
    -&gt;     SELECT T.value,'',@given_locale_id FROM terms T
    -&gt; ) A
    -&gt; WHERE
    -&gt;     locale_id = @given_locale_id
    -&gt; GROUP By terms_value;
+---------------+------------------------------------------+
| terms_value   | rosetta_value                            |
+---------------+------------------------------------------+
| lname         | Username:                                |
| lpass         | Password:                                |
| welcome_blurb | Welcome to appland! Please log in below. |
+---------------+------------------------------------------+
3 rows in set (0.00 sec)

mysql&gt;


Let's try it against locale_id 2

mysql&gt; SET @given_locale_id = 2;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT terms_value,MAX(rosetta_value) rosetta_value
    -&gt; FROM
    -&gt; (
    -&gt;     SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    -&gt;     FROM terms T LEFT JOIN rosetta R
    -&gt;     ON T.terms_id=R.terms_id
    -&gt;     UNION
    -&gt;     SELECT T.value,'',@given_locale_id FROM terms T
    -&gt; ) A
    -&gt; WHERE
    -&gt;     locale_id = @given_locale_id
    -&gt; GROUP By terms_value;
+---------------+-------------------------------------------------+
| terms_value   | rosetta_value                                   |
+---------------+-------------------------------------------------+
| lname         | Benutzername:                                   |
| lpass         | Passwort:                                       |
| welcome_blurb | Welcome to appland Bitte melden Sie sich unten. |
+---------------+-------------------------------------------------+
3 rows in set (0.00 sec)

mysql&gt;


Let's try it against locale_id 3

mysql&gt; SET @given_locale_id = 3;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT terms_value,MAX(rosetta_value) rosetta_value
    -&gt; FROM
    -&gt; (
    -&gt;     SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    -&gt;     FROM terms T LEFT JOIN rosetta R
    -&gt;     ON T.terms_id=R.terms_id
    -&gt;     UNION
    -&gt;     SELECT T.value,'',@given_locale_id FROM terms T
    -&gt; ) A
    -&gt; WHERE
    -&gt;     locale_id = @given_locale_id
    -&gt; GROUP By terms_value;
+---------------+---------------+
| terms_value   | rosetta_value |
+---------------+---------------+
| lname         |               |
| lpass         |               |
| welcome_blurb |               |
+---------------+---------------+
3 rows in set (0.00 sec)

mysql&gt;


I tried it in your SQL Fiddle and all three locale_ids work as advertised.

Give it a Try !!!