SQL find missing language entries in table

I have a table which is missing some entries for a certain language. How can I get a list of all language text in english (lang 1 in table) which is missing the foreign translation counterpart (lang 2)

My table is as follows

PageName | LanguageNo | TranslationName | TranslationText   |
main     |     1      | SomeName        | some english text |
main     |     2      | SomeName        | some foreign text |
main     |     1      | SomeName2       | some english 2    |
other    |     1      | SomeName3       | some english 3    |
other    |     2      | SomeName3       | some foreign 3    |

For example, using the above table data, only the following should be returned:

main     |     1      | SomeName2       | some english 2    |

How can I write a SQL statement to achieve this?

Thanks

Answers


There are several methods, but here is one that uses not exists:

select t.*
from mytable t
where t.LanguageNo = 1 and
      not exists (select 1
                  from mytable t2
                  where t2.pagename = t.pagename and 
                        t2.translationname = t.translationname and
                        t2.LanguageNo = 2
                 );

Here is a SQL Fiddle.


You can try the following:

-- Create demo data
CREATE TABLE #translation(pageName nvarchar(10), LanguageNo int, TranslationName nvarchar(25), TranslationText nvarchar(50))

INSERT INTO #translation(pageName, LanguageNo, TranslationName, TranslationText)
VALUES  ('main',1,'SomeName','some english text'),
        ('main',2,'SomeName','some foreign text'),
        ('main',1,'SomeName2','some english 2'),
        ('other',1,'SomeName3','some english 3'),
        ('other',2,'SomeName3','some foreign 3')
        --,('other',3,'SomeName3','some foreign 3') -- uncomment for language3 demo

-- your work:
SELECT availTrans.*
FROM #translation t
-- get all needed combinations
RIGHT JOIN(
        SELECT DISTINCT t.pageName, t.TranslationName, langs.LanguageNo
        FROM #translation as t
        CROSS JOIN (SELECT DISTINCT LanguageNo FROM #translation) as langs
    ) as availTrans
    ON t.pageName = availTrans.pageName
    AND t.TranslationName = availTrans.TranslationName
    AND t.LanguageNo = availTrans.LanguageNo
WHERE t.pageName IS NULL

-- Cleanup
DROP TABLE #translation

Given input:

pageName   LanguageNo  TranslationName           TranslationText
---------- ----------- ------------------------- ---------------------
main       1           SomeName                  some english text
main       2           SomeName                  some foreign text
main       1           SomeName2                 some english 2
other      1           SomeName3                 some english 3
other      2           SomeName3                 some foreign 3

Which produces this result:

pageName   TranslationName           LanguageNo
---------- ------------------------- -----------
main       SomeName2                 2

You can also use SUM with OVER() like this.

Query

;WITH CTE AS
(
    SELECT pageName, LanguageNo, TranslationName, TranslationText,
    SUM(CASE WHEN LanguageNo = 1 THEN 1 ELSE 0 END) OVER(PARTITION BY TranslationName) L1,
    SUM(CASE WHEN LanguageNo = 2 THEN 1 ELSE 0 END)  OVER(PARTITION BY TranslationName) L2
    FROM #translation t
)
SELECT pageName, LanguageNo, TranslationName, TranslationText FROM CTE
WHERE L1 >=1 AND L2 = 0

Output

pageName    LanguageNo  TranslationName TranslationText
main    1   SomeName2   some english 2

SELECT t1.*
  FROM tblTranslation t1
  LEFT JOIN tblTranslation t2 
    ON t2.TranslationName = t1.TranslationName        
   AND t2.LanguageNo = 2
 WHERE t2.TranslationName IS NULL
   AND t1.LanguageNo = 1

SELECT t1.*
FROM tblTranslation t1
LEFT JOIN tblTranslation t2 ON t2.PageName = t1.PageName  
                            AND t2.TranslationName = t1.TranslationName 
                            AND t2.LanguageNo = 2
WHERE t1.LanguageNo = 1
AND t2.LanguageNo IS NULL

Need Your Help

Can you return a render() without a context?

python django django-views

I have a Django template I'm rendering, however, I have no need to give a context to it (I don't use any variable in the template).