Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 20th, 2009, 09:49 PM
Registered User
 
Join Date: Oct 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Assistance with duplicate values

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)
Reply With Quote
  #2 (permalink)  
Old October 20th, 2009, 10:11 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old October 20th, 2009, 10:25 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old October 20th, 2009, 10:40 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Figured most of it out, but can't make sense of this one...
Code:
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 
)
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:
Code:
AND LanguageVersion.LanguageVersionID IN 
(
--this inner query will return the LanguageVersionID of the record.
SELECT LanguageVersion.LanguageVersionID 
FROM LanguageVersion 
WHERE LanguageVersion.UserID = @UserID 
)
How is that any different than simply doing
Code:
AND LanguageVersion.UserID = @UserID
(Answer: it isn't.)
Reply With Quote
  #5 (permalink)  
Old October 20th, 2009, 10:45 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #6 (permalink)  
Old October 20th, 2009, 11:24 PM
Registered User
 
Join Date: Oct 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

duplicated post

Last edited by fattmatt; October 21st, 2009 at 05:14 PM.
Reply With Quote
  #7 (permalink)  
Old October 21st, 2009, 04:26 AM
Registered User
 
Join Date: Oct 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Old Pedant View Post
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!!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
I need to omit the duplicate values when displayin ppakala XSLT 1 June 24th, 2008 06:13 PM
How Do I avoid duplicate values xsltier XSLT 3 June 4th, 2008 01:36 PM
Detecting duplicate values Europom Excel VBA 2 May 29th, 2007 07:09 AM
how can i check for duplicate values before saving noor ASP.NET 1.0 and 1.1 Basics 3 June 10th, 2005 09:28 AM



All times are GMT -4. The time now is 08:31 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.