Storing english and french translation in same table and its mapping in another table .Have entered 3 english words and stored translation for 2 words.
How can I get the recordcount of words that is not having translation?
declare @myInfo table (ID int,languageID int,info nvarchar(50))
Insert into @myInfo values
(1,1,'hello')
,(2,1,'world')
,(3,1,'welcome')
--frech translation
,(4,2,'bonjour')
,(5,2,'monde')
select * from @myInfo
declare @myMapping table (sourceID int,translatedID int)
Insert into @myMapping values
(1,4)
,(2,5)
select * from @myMapping
SELECT * FROM @myInfo i
WHERE i.languageID = 1
AND NOT EXISTS
(
SELECT *
FROM @myInfo i2
INNER JOIN @myMapping m ON
m.translatedID = i2.ID
WHERE
m.sourceID = i.ID
AND i2.languageID = 2
);
You've got this modeled improperly. The "WordID" should be the same for all versions of the same word. Then simply use the combination of WordID & LanguageID as a compound primary key. Doing so would allow you to easily switch word translations with a parameter on language and there would be no need to maintain a separate mapping table.
declare @translation_source table(ID int,word nvarchar(50));
insert into @translation_source values
(101,'hello')
,(102,'world')
,(103,'welcome');
declare @translated_text table (textID int,wordID int,langID int,translatedText nvarchar(100));
insert into @translated_text values
(1,101,201,'bonjour'),(2,102,201,'monde');
-- no translations exist...
SELECT
*
FROM
@translation_source ts
WHERE
NOT EXISTS (SELECT * FROM @translated_text tt WHERE ts.ID = tt.wordID);
-- Translation count per word...
SELECT
ts.ID,
ts.word,
tc.TransCount
FROM
@translation_source ts
CROSS APPLY (
SELECT
COUNT(1)
FROM
@translated_text tt
WHERE
ts.ID = tt.wordID
) tc (TransCount);
Output...
ID word
----------- --------------------------------------------------
103 welcome
ID word TransCount
----------- -------------------------------------------------- -----------
101 hello 1
102 world 1
103 welcome 0