0

I have a translation table with a column that contains values repeatedly (language name). As following:

id | neutral text | language | translation
---+--------------+----------+------------
 0 |       SUBMIT |       en |      Submit
 1 |       SUBMIT |       cs |     Odeslat
 2 |       SUBMIT |       fr |    Démarrer
 3 |          DAY |       fr |        Jour
 4 |       MONDAY |       fr |       Lundi
 5 |       MONDAY |       cs |     Pondělí

Now, as you can see, I have intentionally omitted to fill all the translations for DAY and MONDAY. What I need to find out by a query is, which records are incomplete. This means, find cells that have less occurrences for "neutral text" than others.
Is there possible anything besides FOR loop in PHP? Downloading whole table and looping it makes MySql useless at this moment.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Tomáš Zato
  • 50,171
  • 52
  • 268
  • 778

1 Answers1

1

You can do this with aggregation and a having clause:

select neutraltext
from t
group by neutraltext
having count(*) < (select count(*) from t group by neutraltext order by count(*) desc limit 1);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I just discovered that for more than 2 languages, this makes it impossible to determine which language is missing the translation. If I do a query on DB where 2 languages are present, [this is what I get](http://pasteboard.co/euqpQTZ.png). – Tomáš Zato Sep 15 '14 at 23:18
  • In other words, the retrieved information is insufficient. I need to know which translation(s) are missing. I have not learned enough about MySQL to figure it myself unfortunatelly. – Tomáš Zato Sep 15 '14 at 23:19
  • @TomášZato . . . If you want to know the specific languages, I would suggest that you ask another question. Be quite specific on what you need for the desired results. – Gordon Linoff Sep 16 '14 at 02:18