Query for localization app

Question

Here are my tables:

mysql> SELECT * FROM locale;
+-----------+---------+
| locale_id | value   |
+-----------+---------+
|         1 | English |
|         2 | Deutch  |
|         3 | French  |
+-----------+---------+

mysql> SELECT * FROM terms;
+----------+---------------+
| terms_id | value         |
+----------+---------------+
|        1 | lname         |
|        2 | lpass         |
|        3 | welcome_blurb |
+----------+---------------+

mysql> 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. |
+------------+-----------+----------+-------------------------------------------------+

What I want is a query that contains the locale_id, and it returns all the terms.value and rosetta.value for that locale, if it exists. If it hasn't been defined in rosetta table yet, then have empty values. I'd like it to 'just work' via clever JOINs, and not have IF logic in there if possible.

Desired output:

If I give the query locale_id=1:

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

This one I can get working just fine with:

SELECT terms.value,rosetta.value as definition
FROM terms
LEFT JOIN rosetta ON terms.terms_id=rosetta.terms_id
WHERE rosetta.locale_id=1 OR rosetta.locale_id IS NULL;

If I give the query locale_id=3, what I want back is:

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

However, I just get an empty result.

I was thinking something like this could work:

SELECT terms.value,
IF(rosetta.locale_id=3,rosetta.value,'') as definition
FROM terms
LEFT JOIN rosetta ON terms.terms_id=rosetta.terms_id
WHERE rosetta.locale_id=3 OR rosetta.locale_id IS NULL;

However it doesn't. Any suggestions? Really banging my head against a wall with this one.

Link to the SQL Fiddle with the content.

Answer 1

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> DROP DATABASE IF EXISTS timhaserback;
Query OK, 3 rows affected (0.14 sec)

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

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

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

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

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

mysql> 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)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> 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.');
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

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

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

mysql> 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>

Let's try it against locale_id 1

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

mysql> 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;
+---------------+------------------------------------------+
| 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>

Let's try it against locale_id 2

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

mysql> 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;
+---------------+-------------------------------------------------+
| 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>

Let's try it against locale_id 3

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

mysql> 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;
+---------------+---------------+
| terms_value   | rosetta_value |
+---------------+---------------+
| lname         |               |
| lpass         |               |
| welcome_blurb |               |
+---------------+---------------+
3 rows in set (0.00 sec)

mysql>

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

Give it a Try !!!