SQLTeam.com | Weblogs | Forums

Find the record count with same table

sql2012

#1

Hi,

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

#2
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
);

#3

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.


#4

Thanks JamesK.
Have revised table as per Jason_A_Long suggestion.

declare @translation_source table(ID int,word nvarchar(50))

declare @translation_languages table(gID int,lang_name varchar(50),lang_code varchar(10))

declare @translated_text table (textID int,wordID int,langID int,translatedText nvarchar(100))

insert into @translation_source values
(101,'hello')
,(102,'world')
,(103,'welcome')


insert into @translation_languages values(200,'English','EN'),(201,'French','FR'),(202,'Arabic','AR')

select * from @translation_source
select * from @translation_languages

insert into @translated_text values
(1,101,201,'bonjour'),(2,102,201,'monde')

-- Arabic

insert into @translated_text values(31,101,202,N'مرحبا')

select * from @translated_text

#5

Something along these lines?

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