CODEWITHSUNDEEP
mysqlsqldatabase
Owen
Owen

Reputation: 790

Select missing rows

I'm currently building a multilingual website, where content is added in English first and than translated into other languages.

All the content is stored in one MySQL table that looks like this:

id | language | title               | master
1  | en       | Page one title      | 1
2  | es       | Página un título    | 1
3  | de       | Seite einen titel   | 1
4  | en       | Page two title      | 2
5  | es       | Página dos título   | 2

What I need to do is be able to get a list of all the content that still needs to be translated. In the case above it would be #4 needs to be translated into German.

Any help would be appreciated.
Thanks,

Upvotes: 2

Views: 159

Answers (4)

Glenn
Glenn

Reputation: 9170

  • Find all the languages in use.
  • Find all the master documents in use.
  • Create a list of all master documents in all languages.

  • Remove the list of documents already translated (MINUS or EXCEPT depending 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

Upvotes: 0

pete
pete

Reputation: 25091

I would use this to find the content and 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 helps,

Pete

Upvotes: 2

Eugen Rieck
Eugen Rieck

Reputation: 65332

If you want not only the count, but also the info what is missing you migh want to try

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

Upvotes: 2

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171559

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

Upvotes: 8

Related Questions