Finding records that don't have a translation

Databases
1

Hi,
I am back with another question: I love SPFs!

I have a table which holds all the language strings for my website.
A string is made up of three parts: the variable name (ie lang_home) of the string, the text associated with this string (ie “Home”) and the language this is in. None of these is unique.
I would like to query my DB to help me figure out which variables have not been translated. Here following are the DB scheme and some sample data:

CREATE TABLE locales (
  id mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `text` text NOT NULL,
  lang varchar(5) NOT NULL,
  mod_time int(10) unsigned NOT NULL,
  PRIMARY KEY (id),
  KEY lang (lang),
  KEY `name` (`name`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

INSERT INTO locales SET name = 'lang_home', text = 'Home', lang = 'en-US';
INSERT INTO locales SET name = 'lang_home', text = 'Casa', lang = 'it-IT';
INSERT INTO locales SET name = 'lang_test', text = 'Test', lang = 'en-US';

In this case, I would like to query the DB for missing it-IT translations and have it return ‘lang_test’.
How could I do this?

Thanks,
Adrien

2 
SELECT x.name
  FROM ( SELECT DISTINCT name FROM locales ) AS x
LEFT OUTER
  JOIN locales
    ON locales.name = x.name
   AND locales.lang = 'it-IT'
 WHERE locales.name IS NULL

:slight_smile:

3

Thanks 937, you are great!

4

thanks, but it’s actually SQL that is great

such a complex problem, and such an elegant solution

can you imagine how a php programmer would do it? :confused:

5

yeah, that’s exactly what i was tying to avoid :slight_smile: