Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Unanswered: Assistance with SQL Query

    I am attempting to develop an asp.net (c#) multilingual website where users can store information in numerous languages (French, Spanish, English etc) with a SQL Server 2008 Database.

    Users can store information in a number of languages; in effect they can have many language versions of their information (information stored in English, Polish, Hindi etc).

    When the user creates a new language version of their information, the language version is saved in that language. For example, if the user is viewing the site in English and the user creates a new language version in French, then the database saves the language version as French - France (I also store the country details). If the user is viewing the website in German and the user creates a new language version in English, then the database saves the language version as Englisch – Singapur (in English this is English - Singapore).

    I have an icon on each page that the user can mouse over and at a glance a SQL stored procedure returns the language versions that the user has stored to the website.

    The problem I have is that the Stored Procedure returns the Language Version details in the language that the user saved the details in the language that is was saved in by the user. For example:

    STORED PROCEDURE QUERY – USER LANGUAGES
    Code:
    SELECT 
    LanguageVersion.LanguageVersionID, 
    LanguageVersion.LanguageAvailableID, 
    LanguagesAvailable.LanguageAvailableDescriptionIntl, 
    CountryFlag.CountryFlagIconPath 
    
    FROM 
    LanguageVersion, 
    LanguagesAvailable, 
    Country, 
    CountryFlag 
    
    WHERE LanguageVersion.LanguageAvailableID = LanguagesAvailable.LanguageAvailableID 
    AND LanguagesAvailable.CountryID = Country.CountryID 
    AND Country.CountryFlagID = CountryFlag.CountryFlagID 
    AND LanguageVersion.UserID = @UserID 
    
    ORDER BY LanguagesAvailable.LanguageAvailableDescriptionIntl ASC
    STORED PROCEDURE RESULTS – USER LANGUAGES
    LanguageVersionID LanguageAvailableID LanguageAvailableDescriptionIntl CountryFlagIconPath
    1540 4304 Anglais - Turks et Caïques, Îles ~/Images/Flags/101.gif
    1536 4957 Deutsch - Deutschland ~/Images/Flags/138.gif
    1265 37 English - Australia ~/Images/Flags/36.gif
    1532 11007 Español - España ~/Images/Flags/177.gif
    1534 4326 Français - France ~/Images/Flags/116.gif
    1539 12713 French - Belgium ~/Images/Flags/29.gif
    1537 12758 German – Liechtenstein ~/Images/Flags/139.gif
    1538 12792 Spanish - Chile ~/Images/Flags/164.gif
    No rows affected.
    (8 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[usp_UserLanguages].

    Row 1 & 5 are in the French language (saved while viewing the website in the French Language), Row 2 is in the German language (saved while viewing the website in the German Language), Row 3, 6, 7 & 8 are in the English language (saved while viewing the website in the English Language), Row 4 is in the Spanish language (saved while viewing the website in the Spanish Language). These are the LanguageAvailableDescriptionIntl’s that you will see no matter the language that the user is viewing the website in.

    Here are my related tables:

    LanguageVersion (holds the language versions of the users information that they have saved to the DB)
    LanguageVersionID int (PK)
    UserID uniqueidentifier (FK)
    LanguageAvailableID int (FK)

    LanguagesAvailable (holds the different languages that the user can use to save their data to the DB)
    LanguageAvailableID int (PK)
    LanguageAvailableDescriptionNative nvarchar(255)
    NativeLanguageCode varchar(10)
    AltNativeLanguageCode varchar(10)
    LanguageAvailableDescriptionIntl nvarchar(255)
    LanguageAvailableDescriptionEng nvarchar(255)
    CountryID int (FK)
    Culture varchar(7)
    LanguageCode varchar(10)
    LanguageID int (FK)

    Country (the country details)
    CountryID int (PK)
    CountryShortNameIntl nvarchar(255)
    CountryShortNameEng nvarchar(255)
    CountryLongNameIntl nvarchar(255)
    CountryLongNameEng nvarchar(255)
    CountryFlagID int (FK)
    CountryCode varchar(10)
    CountryLanguageCode varchar(10)
    AltCountryID int

    CountryFlag (holds the flag icon details for the country)
    CountryFlagID int (PK)
    CountryFlagCountryName nvarchar(255)
    CountryFlagIconPath nvarchar(255)

    LanguageView (holds the current language version the user is viewing the site in)
    LanguageViewID int (PK)
    UserID uniqueidentifier (FK)
    LanguageVersionID int (FK)

    To attempt to overcome this problem, I wrote the following Stored Procedure:

    NEW STORED PROCEDURE QUERY – USER LANGUAGES
    Code:
    SELECT 
    LanguageVersion.LanguageVersionID, 
    LanguageVersion.LanguageAvailableID, 
    LanguagesAvailable.LanguageAvailableDescriptionIntl, 
    CountryFlag.CountryFlagIconPath 
    
    FROM LanguagesAvailable, Country, CountryFlag, LanguageVersion 
    
    WHERE LanguagesAvailable.CountryID = Country.CountryID 
    AND LanguageVersion.UserID = @UserID 
    AND Country.CountryFlagID = CountryFlag.CountryFlagID 
    
    AND LanguagesAvailable.LanguageAvailableDescriptionEng IN (
    	--this inner query will return the english name of the language that the user has saved to the language version table (English - Ireland). 
    	--the LanguageAvailableDescriptionEng is the English version of the language available.
    	SELECT LanguagesAvailable.LanguageAvailableDescriptionEng 
    	FROM LanguagesAvailable, LanguageVersion 
    	WHERE LanguagesAvailable.LanguageAvailableID = LanguageVersion.LanguageAvailableID 
    	AND LanguageVersion.UserID = @UserID 
    )
    
    AND LanguagesAvailable.LanguageCode = (
    	--this inner query will return the current language code (fr-FR) of the user.
    	--determines the language that the user is viewing the web site in, independant of the language details of the browser.
    	SELECT LanguagesAvailable.AltNativeLanguageCode 
    	FROM LanguagesAvailable, LanguageVersion, LanguageView 
    	WHERE LanguagesAvailable.LanguageAvailableID = LanguageVersion.LanguageAvailableID 
    	AND LanguageVersion.LanguageVersionID = LanguageView.LanguageVersionID 
    	AND LanguageView.UserID = @UserID 
    )
    
    AND LanguageVersion.LanguageAvailableID IN (
    	--this inner query will return the LanguageAvailableID of the record.
    	SELECT LanguagesAvailable.LanguageAvailableID 
    	FROM LanguagesAvailable, LanguageVersion 
    	WHERE LanguageVersion.UserID = @UserID 
    	AND LanguagesAvailable.LanguageAvailableID = LanguageVersion.LanguageAvailableID 
    )
    
    ORDER BY LanguagesAvailable.LanguageAvailableDescriptionIntl ASC
    This gave me the following results. The following results are predicated upon the user viewing the website in the French language (fr-FR).

    NEW STORED PROCEDURE RESULTS – USER LANGUAGES
    LanguageVersionID LanguageAvailableID LanguageAvailableDescriptionIntl CountryFlagIconPath
    1265 37 Allemagne – Liechtenstein ~/Images/Flags/139.gif
    1532 11007 Allemagne - Liechtenstein ~/Images/Flags/139.gif
    1534 4326 Allemagne - Liechtenstein ~/Images/Flags/139.gif
    1536 4957 Allemagne - Liechtenstein ~/Images/Flags/139.gif
    1537 12758 Allemagne - Liechtenstein ~/Images/Flags/139.gif
    1538 12792 Allemagne - Liechtenstein ~/Images/Flags/139.gif
    1539 12713 Allemagne - Liechtenstein ~/Images/Flags/139.gif
    1540 4304 Allemagne - Liechtenstein ~/Images/Flags/139.gif
    1539 12713 Allemand - Allemagne ~/Images/Flags/138.gif
    1540 4304 Allemand - Allemagne ~/Images/Flags/138.gif
    1537 12758 Allemand - Allemagne ~/Images/Flags/138.gif
    1538 12792 Allemand - Allemagne ~/Images/Flags/138.gif
    1534 4326 Allemand - Allemagne ~/Images/Flags/138.gif
    1536 4957 Allemand - Allemagne ~/Images/Flags/138.gif
    1265 37 Allemand - Allemagne ~/Images/Flags/138.gif
    1532 11007 Allemand - Allemagne ~/Images/Flags/138.gif
    1534 4326 Anglais - Australie ~/Images/Flags/36.gif
    1265 37 Anglais - Australie ~/Images/Flags/36.gif
    1532 11007 Anglais - Australie ~/Images/Flags/36.gif
    1537 12758 Anglais - Australie ~/Images/Flags/36.gif
    1536 4957 Anglais - Australie ~/Images/Flags/36.gif
    1538 12792 Anglais - Australie ~/Images/Flags/36.gif
    1539 12713 Anglais - Australie ~/Images/Flags/36.gif
    1540 4304 Anglais - Australie ~/Images/Flags/36.gif
    1540 4304 Anglais - Turks et Caïques, Îles ~/Images/Flags/101.gif
    1539 12713 Anglais - Turks et Caïques, Îles ~/Images/Flags/101.gif
    1538 12792 Anglais - Turks et Caïques, Îles ~/Images/Flags/101.gif
    1536 4957 Anglais - Turks et Caïques, Îles ~/Images/Flags/101.gif
    1537 12758 Anglais - Turks et Caïques, Îles ~/Images/Flags/101.gif
    1532 11007 Anglais - Turks et

    (REMAINED OF RESULTS CULLED TO FIT DESCRIPTION INTO THREAD)

    No rows affected.
    (64 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[_TestStoredProcedure_WorkingCopy].

    As you can see, the new SP is now retuning duplicate values. I am unable to get rid of the duplicate values so that I can have the following required results:

    LanguageVersionID LanguageAvailableID LanguageAvailableDescriptionIntl CountryFlagIconPath
    ------------------------------------------------------------------------------------------------------
    1536 4957 Allemand - Allemagne ~/Images/Flags/138.gif
    1537 12758 Allemagne - Liechtenstein ~/Images/Flags/139.gif
    1265 37 Anglais - Australie ~/Images/Flags/36.gif
    1540 4304 Anglais - Turks et Caïques, Îles ~/Images/Flags/101.gif
    1538 12792 Espagnol - Chili ~/Images/Flags/164.gif
    1532 11007 Espagnol - Espagne ~/Images/Flags/177.gif
    1539 12713 Français - Belgique ~/Images/Flags/29.gif
    1534 4326 Français - France ~/Images/Flags/116.gif
    No rows affected.
    (8 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[usp_UserLanguages].

    Can anyone point out my problem to delete the duplicate values issue?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I've been trying to understand what you wrote, but I failed. I can't make much sense out of why you also want to store county information with the language.
    If the user is viewing the website in German and the user creates a new language version in English, then the database saves the language version as Englisch – Singapur (in English this is English - Singapore).
    To me, this Signapur thing falls right out of the blue sky.

    I'm totally in the dark about the necessity of the tables LanguageVersion and LanguageAvailable. But perhaps the table names are putting me on the wrong foot.

    If I understand your problem properly, these are the tables you need:
    Code:
    Table UserProfile
    UserId
    LanguageId
    CountryId
    ...
    
    Table Country
    CountryId
    ...
    
    Table Language
    LanguageId
    ...
    
    Table CountryLanguages
    CountryId
    LanguageId
    
    Table Information
    UserId
    LanguageId
    Message NVARCHAR(max)
    Perhaps the reason why you are getting these duplicates is also due to the fact that you are storing your business data in the language of the user, by storing the very same information over and over again in French, English, German, Hindi, ...

    Use a translation table to display words in another language.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •