CREATE TABLE translations (
  id BIGINT NOT NULL AUTO_INCREMENT,
  lang_code CHAR(5) NOT NULL,
  item_id BIGINT NOT NULL,
  item_name VARCHAR(255) NOT NULL,
  item_desciption TEXT NULL,
  PRIMARY KEY(id)
);

SELECT i.id, i.level, t.item_name, t.item_desciption
  FROM items i, translations t
  WHERE t.lang_code = 'en_US'
  AND i.id = t.item_id
  AND i.level = 3
  ORDER BY t.name;

SELECT i.id, i.level, t.item_name, t.item_desciption
  FROM items i, translations t
  WHERE i.id = t.item_id
  AND i.level = 3
  GROUP BY i.id
  ORDER BY t.name;

#!/bin/sh
sqlite3 blubb.db <<EOD
CREATE TABLE items(id INTEGER, level INTEGER, PRIMARY KEY(id));
INSERT INTO items VALUES(1, 1);
INSERT INTO items VALUES(2, 1);
INSERT INTO items VALUES(3, 2);
INSERT INTO items VALUES(4, 3);
INSERT INTO items VALUES(5, 3);
INSERT INTO items VALUES(6, 3);

CREATE TABLE translations(id INTEGER, lang TEXT, name TEXT, UNIQUE(id, lang));
INSERT INTO translations VALUES(1, 'en', 'buy');
INSERT INTO translations VALUES(1, 'de', 'kaufen');
INSERT INTO translations VALUES(1, 'is', 'kaupa');
INSERT INTO translations VALUES(1, 'fr', 'acheter');
INSERT INTO translations VALUES(2, 'en', 'sell');
INSERT INTO translations VALUES(2, 'is', 'selja');
INSERT INTO translations VALUES(3, 'is', 'laera');
INSERT INTO translations VALUES(3, 'fr', 'apprendre');
INSERT INTO translations VALUES(4, 'de', 'trinken');
INSERT INTO translations VALUES(4, 'en', 'drink');
INSERT INTO translations VALUES(4, 'is', 'drekka');
INSERT INTO translations VALUES(5, 'en', 'quit');
INSERT INTO translations VALUES(6, 'fr', 'annuler');
EOD

#!/usr/bin/perl -T
use strict;
use warnings;

use DBI;

my $db = DBI->connect('dbi:SQLite:dbname=blubb.db',
              '', '',
              { RaiseError => 1 });


my $stmt = $db->prepare(<<'EOD');
  SELECT
    (CASE
       WHEN t.lang ISNULL THEN
         (SELECT name FROM translations WHERE id = i.id)
       ELSE
         t.name
     END) AS name
  FROM
    items AS i LEFT OUTER JOIN translations AS t
  ON
    i.id = t.id AND t.lang = ?
  WHERE i.id = ? AND i.level = ?;
EOD

sub x {
  printf "lang = %s, id = %d, level = %d  ==>  ", @_;
  $stmt->execute(@_);
  print $stmt->fetchrow_array();
  print "\n";
}

x('is', 2, 1);
x('de', 2, 1);

$stmt->finish();
$db->disconnect();