Quote:
Originally Posted by Old Pedant
Well, start by showing us the results of each of those inner SELECTs, for the same given @userid.
That is, for example:
Code:
SELECT LanguagesAvailable.LanguageAvailableDescriptionEng
FROM LanguagesAvailable, LanguageVersion
WHERE LanguagesAvailable.LanguageAvailableID = LanguageVersion.LanguageAvailableID
AND LanguageVersion.UserID = @UserID
And ditto for the other 3. |
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!!
Assistance with duplicate values
