SELECT Statement - duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT Statement - duplicates

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-03 : 07:57:00

Hi,

I have the following select statement which outputs results as shown below;

SELECT     dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc, dbo.aspnet_InterestGroupBListA.InterestGroupBListADesc
FROM         dbo.aspnet_cprdContacts INNER JOIN
                      dbo.aspnet_AreaofInterest ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_AreaofInterest.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupA ON dbo.aspnet_AreaofInterest.InterestCode = dbo.aspnet_InterestGroupA.InterestGroupAID INNER JOIN
                      dbo.aspnet_ObservationalResearch ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_ObservationalResearch.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupBListA ON 
                      dbo.aspnet_ObservationalResearch.ObsResearchCode = dbo.aspnet_InterestGroupBListA.InterestGroupBListAID
WHERE     (dbo.aspnet_cprdContacts.Surname = 'Mascrier')

Results

Surname InterestGroupADesc InterestGroupBListADesc

Mascrier Biologics DataLink
Mascrier Biologics Device Related
Mascrier Biologics Drug
Mascrier Biologics Genetic
Mascrier Devices DataLink
Mascrier Devices Device Related
Mascrier Devices Drug
Mascrier Devices Genetic
Mascrier Health DataLink
Mascrier Health Device Related
Mascrier Health Drug
Mascrier Health Genetic
Mascrier IT DataLink
Mascrier IT Device Related
Mascrier IT Drug
Mascrier IT Genetic

Expected Result

Surname InterestGroupADesc InterestGroupBListADesc

Mascrier Biologics DataLink
Mascrier Devices Device Related
Mascrier Health Drug
Mascrier IT Genetic

any help pls..

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-03 : 08:07:11

I see that you have one row from each combination of the first two columns. What is the logic that you used to pick the one row in each of those combinations? For example, what is the logic for picking the fourth row out of these four rows:

Mascrier IT DataLink
Mascrier IT Device Related
Mascrier IT Drug
Mascrier IT Genetic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-03 : 08:51:11

I dont understand the question.. What the name 'Mascrier' has saved in the database is

InterestGroupADesc table - (aspnet_AreaofInterest)
Biologics
Devices
Health
IT

InterestGroupBListADesc - table (dbo.aspnet_ObservationalResearch)
Datalink
Device Related
Drug
Genetic

So I want the query to have two columns for each interest, surname retained

Therefore, something like;

Surname InterestGroupADesc InterestGroupBListADesc

Mascrier Biologics DataLink
Mascrier Devices Device Related
Mascrier Health Drug
Mascrier IT Genetic

For example we have an extra data for InterestGroupADesc = Chemicals

The result will be:

Surname InterestGroupADesc InterestGroupBListADesc

Mascrier Biologics DataLink
Mascrier Devices Device Related
Mascrier Health Drug
Mascrier IT Genetic
Mascrier Chemicals NULL

Any help!! Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 09:36:12

still not clear. you have not provided answer to question asked.
how do you determine which value to be returned for InterestGroupBListADesc out of set of values for a Surname, InterestGroupADesc combination?

ie in first example you've

Mascrier Biologics DataLink
Mascrier Biologics Device Related
Mascrier Biologics Drug
Mascrier Biologics Genetic
in data out of which you chose row with value DataLink. what was basis for that?

please keep in mind that there's no concept of first or last in table so unless you specify order in terms of another column, you cant guarantee the value it will return

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-03 : 11:56:16

Ok hope I can be clearer now,

All these 3 tables have different information..

I want to link the surname and import the InterestGroupBListADesc and InterestGroupADesc for that surname within a query.
Which would be the best way of doing it..

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 12:00:12

Nope...you're not clear still
while linking you've same values from ADesc coming as shown above. In that case, question was whats the factor that determine which one Bdesc value to be returned with it when you take distinct of Adesc value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-03 : 12:17:01

Ok - does it mean that icant have the layout as I want, its impossible?

I want just to display the surname and the other detals from the 2 tables.. If it will require me to enter null values am ready to accomodate that..

Any help please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 12:25:23

you can get it. only thing is you need to determine the rule to be followed for that. which linked values you want. if its just a random one you can use like


SELECT Surname,InterestGroupADesc,InterestGroupBListADesc
FROM
(
SELECT     dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc, dbo.aspnet_InterestGroupBListA.InterestGroupBListADesc,ROW_NUMBER() OVER (PARTITION BY dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc ORDER BY NEWID()) AS Rn
FROM         dbo.aspnet_cprdContacts INNER JOIN
                      dbo.aspnet_AreaofInterest ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_AreaofInterest.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupA ON dbo.aspnet_AreaofInterest.InterestCode = dbo.aspnet_InterestGroupA.InterestGroupAID INNER JOIN
                      dbo.aspnet_ObservationalResearch ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_ObservationalResearch.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupBListA ON 
                      dbo.aspnet_ObservationalResearch.ObsResearchCode = dbo.aspnet_InterestGroupBListA.InterestGroupBListAID
WHERE     (dbo.aspnet_cprdContacts.Surname = 'Mascrier')
)t
WHERE Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-03 : 12:44:07

Thanks visakh16 -

I never managed to capture the;

InterestGroupBListADesc
Datalink

Result is :

Mascrier Biologics Genetic
Mascrier Devices Device Related
Mascrier Health Drug
Mascrier IT Genetic

I need the other data value - DataLink instead of having duplicate Genetic..

Many thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-06 : 05:56:48

Hi,

The code below gives me a random InterestGroupBListADesc, and keeps changing whenever I run it!
How can I change it and instead of reading randomly, it provides me with the exact 'InterestGroupBListADesc' for surname 'Mascrier'

Also note: how can create a view rather than a query to run the same code...

Many Thanks

SELECT Surname,InterestGroupADesc,InterestGroupBListADesc
FROM
(
SELECT     dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc, dbo.aspnet_InterestGroupBListA.InterestGroupBListADesc,ROW_NUMBER() OVER (PARTITION BY dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc ORDER BY NEWID()) AS Rn
FROM         dbo.aspnet_cprdContacts INNER JOIN
                      dbo.aspnet_AreaofInterest ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_AreaofInterest.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupA ON dbo.aspnet_AreaofInterest.InterestCode = dbo.aspnet_InterestGroupA.InterestGroupAID INNER JOIN
                      dbo.aspnet_ObservationalResearch ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_ObservationalResearch.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupBListA ON 
                      dbo.aspnet_ObservationalResearch.ObsResearchCode = dbo.aspnet_InterestGroupBListA.InterestGroupBListAID
WHERE     (dbo.aspnet_cprdContacts.Surname = 'Mascrier')
)t
WHERE Rn=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 09:18:40

quote:

Originally posted by dr223

Hi,

The code below gives me a random InterestGroupBListADesc, and keeps changing whenever I run it!
How can I change it and instead of reading randomly, it provides me with the exact 'InterestGroupBListADesc' for surname 'Mascrier'

Also note: how can create a view rather than a query to run the same code...

Many Thanks

SELECT Surname,InterestGroupADesc,InterestGroupBListADesc
FROM
(
SELECT     dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc, dbo.aspnet_InterestGroupBListA.InterestGroupBListADesc,ROW_NUMBER() OVER (PARTITION BY dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc ORDER BY NEWID()) AS Rn
FROM         dbo.aspnet_cprdContacts INNER JOIN
                      dbo.aspnet_AreaofInterest ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_AreaofInterest.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupA ON dbo.aspnet_AreaofInterest.InterestCode = dbo.aspnet_InterestGroupA.InterestGroupAID INNER JOIN
                      dbo.aspnet_ObservationalResearch ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_ObservationalResearch.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupBListA ON 
                      dbo.aspnet_ObservationalResearch.ObsResearchCode = dbo.aspnet_InterestGroupBListA.InterestGroupBListAID
WHERE     (dbo.aspnet_cprdContacts.Surname = 'Mascrier')
)t
WHERE Rn=1

This is what I've been asking from post 1 onwards
whats your rule for getting the exact 'InterestGroupBListADesc' for surname?
is it always first occurance or last occurance also based on alphabetically or on the order of which column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-06 : 09:56:44

Yes first/unique occurence of that 'InterestGroupBListADesc' for that surname specified

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 09:58:39

quote:
Originally posted by dr223

Yes first/unique occurence of that 'InterestGroupBListADesc' for that surname specified

Thanks

first occurance alphabetically or is sequence based on order of some other unique valued column (id column or date column)
just stating first occurance doesnt make any sense as there's no ordering of data inside sql table unless specify it in terms of another column or condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-06 : 10:11:46

first occurence alphabetically will be good..

thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-06 : 11:30:38

any help please... many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 11:32:34

[code]
SELECT Surname,InterestGroupADesc,InterestGroupBListADesc
FROM
(
SELECT dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc, dbo.aspnet_InterestGroupBListA.InterestGroupBListADesc,ROW_NUMBER() OVER (PARTITION BY dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc ORDER BY dbo.aspnet_InterestGroupBListA.InterestGroupBListADesc) AS Rn
FROM dbo.aspnet_cprdContacts INNER JOIN
dbo.aspnet_AreaofInterest ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_AreaofInterest.ContactCode INNER JOIN
dbo.aspnet_InterestGroupA ON dbo.aspnet_AreaofInterest.InterestCode = dbo.aspnet_InterestGroupA.InterestGroupAID INNER JOIN
dbo.aspnet_ObservationalResearch ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_ObservationalResearch.ContactCode INNER JOIN
dbo.aspnet_InterestGroupBListA ON
dbo.aspnet_ObservationalResearch.ObsResearchCode = dbo.aspnet_InterestGroupBListA.InterestGroupBListAID
WHERE (dbo.aspnet_cprdContacts.Surname = 'Mascrier')
)t
WHERE Rn=1

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-06 : 12:18:01

Result:

Mascrier Biologics DataLink
Mascrier Devices DataLink
Mascrier Health DataLink
Mascrier IT DataLink

I will need results to be like;

Mascrier Biologics DataLink
Mascrier Devices Device Related
Mascrier Health Drug
Mascrier IT Genetic

Any help - pls

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 13:38:24

as per alphabetic rule its correct as datalink comes first before Device Related,Drug etc

unless you specify your rules properly, you're not going to get close to your solution!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-07 : 06:37:46

Ok - it seems I m confused with the rule..
What I want is to change the above query to have a result as

Surname InterestGroupADesc InterestGroupBListADesc
Mascrier Biologics DataLink
Mascrier Devices Device Related
Mascrier Health Drug
Mascrier IT Genetic

surname = Mascrier has each of the 4 options in the respective interest tables.

Therefore, I want the surname and all the interests from various groups dsiplayed. If one interest fiel will have extra option the others can be set to NULL .

I hope am clearer

Many thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-07 : 09:38:41

any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 12:22:29

quote:
Originally posted by dr223

any help

i still didnt understand your rule
I want the surname and all the interests from various groups dsiplayed
does that mean you want all interests tied to surname being retrieved?
this means it will duplicate values of surname and Adesc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources