I am presently creating a multilingual website, where submissions are put in British first and than converted into other languages.

All of the submissions are saved in a single MySQL table that appears such as this:


id  language  title                master

1   en        The first page title       1

2   es        Página not título     1

3   p        Seite einen titel    1

4   en        Page two title       2

5   es        Página dos título    2

What I have to do is have the ability to get a listing of all of the content that also must be converted. Within the situation above it might be #4 must be converted into German.

Any help could be appreciated.
Thanks,

select master
from MyTable
group by master
having count(distinct language) < 3

If you would like not just the count, but the info what's missing you migh would like to try

SELECT
  GROUP_CONCAT(language ORDER BY language) as languages_done
FROM tablename
GROUP BY master
HAVING languages_done<>'de,en,es'

I'd make use of this to obtain the content and also the translations still needed.

SELECT    master
          , Languages.language
FROM      MyTable
          LEFT JOIN (
              SELECT    DISTINCT language
              FROM      master
          ) AS Languages
              ON  master.language = Languages.language
WHERE     master.language IS NULL

Hope this can help,

Pete

  • Find all of the languages being used.
  • Find all of the master documents being used.
  • Create a listing of master documents in most languages.
  • Take away the listing of documents already converted (MINUS or EXCEPT based on platform)

    WITH languages AS (SELECT DISTINCT language FROM mytable),
         masters AS (SELECT DISTINCT master FROM mytable)
    SELECT m.master, l.language
      FROM masters m, languages l
    MINUS
    SELECT m.master, m.language
      FROM mytable m