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

My issue is that I have a SQL Stored Procedure that is returning duplicate values.

Try as I might, I cannot eliminate the duplicate values. I have been stumbling over this for a couple of days now and I am still stuck!

Can anyone provide assistance as to how I might solve this duplicate values issue?? It is driving me insane!


Here is my SQL Stored Procedure:

ALTER PROCEDURE dbo._TestStoredProcedure_WorkingCopy

@UserID uniqueidentifier

AS

SELECT LanguagesAvailable.LanguageAvailableDescriptionInt l , CountryFlag.CountryFlagIconPath , LanguageVersion.LanguageVersionID , LanguageVersion.LanguageAvailableID

FROM LanguagesAvailable, Country, CountryFlag, LanguageVersion

WHERE 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 - Australia).
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 (en-AU) of the user.
SELECT LanguagesAvailable.AltNativeLanguageCode
FROM LanguagesAvailable, LanguageVersion, LanguageView
WHERE LanguagesAvailable.LanguageAvailableID = LanguageVersion.LanguageAvailableID
AND LanguageVersion.LanguageVersionID = LanguageView.LanguageVersionID
AND LanguageView.UserID = @UserID
)

AND LanguagesAvailable.CountryID = Country.CountryID

AND Country.CountryFlagID = CountryFlag.CountryFlagID

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
)

AND LanguageVersion.LanguageVersionID IN
(
--this inner query will return the LanguageVersionID of the record.
SELECT LanguageVersion.LanguageVersionID
FROM LanguageVersion
WHERE LanguageVersion.UserID = @UserID
)

ORDER BY LanguagesAvailable.LanguageAvailableDescriptionInt l ASC


This is the (INCORRECT/DUPLICATE VALUES) result of the Query:

LanguageAvailableDescriptionIntl | CountryFlagIconPath | LanguageVersionID | LanguageAvailableID
----------------------------------------------------------------------------------------------------------------------------
Afrikaans-Afrique du Sud | ~/Images/Flags/97.gif | 1265 | 37
Afrikaans-Afrique du Sud | ~/Images/Flags/97.gif | 1518 | 12708
Afrikaans-Afrique du Sud | ~/Images/Flags/97.gif | 1523 | 12900
Afrikaans-Afrique du Sud | ~/Images/Flags/97.gif | 1527 | 207
Afrikaans-Afrique du Sud | ~/Images/Flags/97.gif | 1528 | 11017
Afrikaans-Afrique du Sud | ~/Images/Flags/97.gif | 1529 | 126
Anglais-Australie | ~/Images/Flags/36.gif | 1529 | 126
Anglais-Australie | ~/Images/Flags/36.gif | 1528 | 11017
Anglais-Australie | ~/Images/Flags/36.gif | 1527 | 207
Anglais-Australie | ~/Images/Flags/36.gif | 1523 | 12900
Anglais-Australie | ~/Images/Flags/36.gif | 1518 | 12708
Anglais-Australie | ~/Images/Flags/36.gif | 1265 | 37
Anglais-Etats-Unis | ~/Images/Flags/105.gif | 1265 | 37
Anglais-Etats-Unis | ~/Images/Flags/105.gif | 1518 | 12708
Anglais-Etats-Unis | ~/Images/Flags/105.gif | 1523 | 12900
Anglais-Etats-Unis | ~/Images/Flags/105.gif | 1527 | 207
Anglais-Etats-Unis | ~/Images/Flags/105.gif | 1528 | 11017
Anglais-Etats-Unis | ~/Images/Flags/105.gif | 1529 | 126
Anglais-Saint Martin | ~/Images/Flags/116.gif | 1527 | 207
Anglais-Saint Martin | ~/Images/Flags/116.gif | 1528 | 11017
Anglais-Saint Martin | ~/Images/Flags/116.gif | 1518 | 12708
Anglais-Saint Martin | ~/Images/Flags/116.gif | 1523 | 12900
Anglais-Saint Martin | ~/Images/Flags/116.gif | 1265 | 37
Anglais-Saint Martin | ~/Images/Flags/116.gif | 1529 | 126
Espagnol-Espagne | ~/Images/Flags/177.gif | 1529 | 126
Espagnol-Espagne | ~/Images/Flags/177.gif | 1265 | 37
Espagnol-Espagne | ~/Images/Flags/177.gif | 1523 | 12900
Espagnol-Espagne | ~/Images/Flags/177.gif | 1518 | 12708
Espagnol-Espagne | ~/Images/Flags/177.gif | 1528 | 11017
Espagnol-Espagne | ~/Images/Flags/177.gif | 1527 | 207
Français-France | ~/Images/Flags/116.gif | 1529 | 126
Français-France | ~/Images/Flags/116.gif | 1528 | 11017
Français-France | ~/Images/Flags/116.gif | 1527 | 207
Français-France | ~/Images/Flags/116.gif | 1523 | 12900
Français-France | ~/Images/Flags/116.gif | 1265 | 37
Français-France | ~/Images/Flags/116.gif | 1518 | 12708
No rows affected.
(36 row(s) returned)


Here are the required results of the SQL Stored Procedure

LanguageAvailableDescriptionIntl | CountryFlagIconPath | LanguageVersionID | LanguageAvailableID
----------------------------------------------------------------------------------------------------------------------------
Afrikaans-Afrique du Sud | ~/Images/Flags/97.gif | 1523 | 12900
Anglais-Australie | ~/Images/Flags/36.gif | 1265 | 37
Anglais-Etats-Unis | ~/Images/Flags/105.gif | 1518 | 12708
Anglais-Saint Martin | ~/Images/Flags/116.gif | 1528 | 11017
Espagnol-Espagne | ~/Images/Flags/177.gif | 1527 | 207
Français-France | ~/Images/Flags/116.gif | 1529 | 126
No rows affected.
(6 row(s) returned)


Here are the related tables:

LanguageVersion
LanguageVersionID int (PK)
UserID uniqueidentifier (FK)
LanguageAvailableID int (FK)

LanguagesAvailable
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
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
CountryFlagID int (PK)
CountryFlagCountryName nvarchar(255)
CountryFlagIconPath nvarchar(255)

Well, start by showing us the results of each of those inner SELECTs, for the same given @userid.

That is, for example:And ditto for the other 3.

Hmmm...looks like it is correctly associating the LanguageVersionID and LanguageAvailableID values.

That is, you *want* 1265 with 37, for example.

So the problem appears to be that you aren't linking the language/flag (first pair of fields) with only one of the last pair of fields.

Yep, that's it. The only place where CountryFlag is linked to another table is here:
AND Country.CountryFlagID = CountryFlag.CountryFlagID

And the only place that Country is linked to another table is here:
AND LanguagesAvailable.CountryID = Country.CountryID

And *NO* place is there any link between any of those 3 tables and the LanguageVersion table!

No, the *INNER* Selects do *NOT* count a links, because all uses of tables in them is independent of the main tables, as you have coded it.

I'm not 100% sure, but I *THINK* that there is no reason whatsoever for using inner SELECTs as you do instead just simply joining to the other tables.

Figured most of it out, but can't make sense of this one...
That *seems* to be saying that you want to find things no matter if the LanguageCode or the AltLanguageCode applies.

********

Even if nothing else made you suspicious, this one should have:
How is that any different than simply doing
(Answer: it isn't.)

ANd in any case why do you *NEED* that one inner SELECT?

The one with this comment:
--this inner query will return the current language code (en-AU) of the user.

You aren't *USING* that language code anyplace.

duplicated post

I have posted several replies, but they have not appeared in this post. I am not sure where they have ended up?

Anyway............................................ ...........

The reason that I am adding this inner query, is that the user can save several languages to the website. So if they are viewing the website in German, all the LanguagesAvailable.LanguageAvailableDescriptionInt l details should be in German and not English, if they chose to save their details in English.

For example, the following query returns the LanguagesAvailable.LanguageAvailableDescriptionInt l of the language that the user saved their language details in:

Language Details of User Query (Native)

SELECT
LanguageVersion.LanguageVersionID,
LanguageVersion.LanguageAvailableID,
LanguagesAvailable.LanguageAvailableDescriptionInt l,
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.LanguageAvailableDescriptionInt l ASC

Language Results of User Query (Native)

LanguageVersionID | LanguageAvailableID | LanguageAvailableDescriptionIntl | CountryFlagIconPath
1523 | 12900 | Afrikaans - South Africa | ~/Images/Flags/97.gif
1265 | 37 | English - Australia | ~/Images/Flags/36.gif
1518 | 12708 | English - USA | ~/Images/Flags/105.gif
1529 | 126 | French - France | ~/Images/Flags/116.gif
1528 | 11017 | Inglés - San Martín | ~/Images/Flags/116.gif
1527 | 207 | Spanish - Spain | ~/Images/Flags/177.gif
No rows affected.
(6 row(s) returned)

As you can see the results are in English, except row 5 which was saved in the French Language, hence it is in French and row 1 which is in the Afrikaans language.

The problem with these results is that the LanguageAvailableDescriptionIntl should be in French (or the language the users is currently viewing the website in - Spanish, Portugese, Hebrew, Arabic etc) and not in the language that the user just happed to be viewing the site in when they selected to add a new language to their details.

That is the resaon why I am replacing the users recorded LanguageAvailableDescriptionIntl with the LanguageAvailableDescriptionIntl of the language that the user is viewing the website in based upon the LanguageCode. The LanguageCode is used to record what langauge the user is currently viewing the website in (independant of the browser language settings, which are overridden). This is the purpose of the languageCode and the following query you questioned:

Language Code Query

AND LanguagesAvailable.LanguageCode =
(
--this inner query will return the current language code (en-AU) of the user.
SELECT LanguagesAvailable.AltNativeLanguageCode
FROM LanguagesAvailable, LanguageVersion, LanguageView
WHERE LanguagesAvailable.LanguageAvailableID = LanguageVersion.LanguageAvailableID
AND LanguageVersion.LanguageVersionID = LanguageView.LanguageVersionID
AND LanguageView.UserID = @UserID
)

If I truncate the original query to the following, I get the following results:

Truncated Query

SELECT LanguagesAvailable.LanguageAvailableDescriptionInt l
, CountryFlag.CountryFlagIconPath

FROM LanguagesAvailable, Country, CountryFlag

WHERE 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 - Australia).
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 (en-AU) of the user.
SELECT LanguagesAvailable.AltNativeLanguageCode
FROM LanguagesAvailable, LanguageVersion, LanguageView
WHERE LanguagesAvailable.LanguageAvailableID = LanguageVersion.LanguageAvailableID
AND LanguageVersion.LanguageVersionID = LanguageView.LanguageVersionID
AND LanguageView.UserID = @UserID
)
AND LanguagesAvailable.CountryID = Country.CountryID
AND Country.CountryFlagID = CountryFlag.CountryFlagID
ORDER BY LanguagesAvailable.LanguageAvailableDescriptionInt l ASC

Truncacated Results

LanguageAvailableDescriptionIntl | CountryFlagIconPath
-----------------------------------------------------
Afrikaans - Afrique du Sud | ~/Images/Flags/97.gif
Anglais - Australie | ~/Images/Flags/36.gif
Anglais - Etats-Unis | ~/Images/Flags/105.gif
Anglais - Saint Martin | ~/Images/Flags/116.gif
Espagnol - Espagne | ~/Images/Flags/177.gif
Français - France | ~/Images/Flags/116.gif
No rows affected.
(6 row(s) returned)

As you can see the LanguageAvailableDescriptionIntl are in French, as the user is viewing the website in French (the LanguageCode is fr-FR).



When I attempt to add in the LanguageVersion table, that is when the duplicate values creep into the results.

Any suggections?? I am desperate to solve this!!