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.
| Author |
Topic |
|
johnmaio
Starting Member
9 Posts |
 Posted - 2008-05-27 : 17:08:14
|
| I know I am doing something totally stupid but I can not figure this out....here is my querySELECT dbo.WVisit.StudyID, dbo.WVisit.EligibleFROM dbo.WVisit INNER JOIN dbo.WPARTICIPANTS ON dbo.WVisit.StudyID = dbo.WPARTICIPANTS.STUDYIDand here is the results9210 997777 999210 997777 99why am I getting duplicate results????the results should be9210 997777 99 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-27 : 17:16:19
|
| Add distinct after Select. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-27 : 17:19:27
|
| >>Add distinct after Select.that would work but don't hide the problem...I'm guessing you have 2 participants with associated with each studyid.Since you aren't selecting anything from wparticipants can you remove it from the query?Be One with the OptimizerTG |
|
|
|
johnmaio
Starting Member
9 Posts |
Posted - 2008-05-27 : 17:40:57
|
quote:
Originally posted by TG
>>Add distinct after Select.that would work but don't hide the problem...I'm guessing you have 2 participants with associated with each studyid.Since you aren't selecting anything from wparticipants can you remove it from the query?Be One with the OptimizerTG
I am using info from wparticipants and I still get duplicates...DISTINCT works but i don't understand why i get dups |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-27 : 20:19:15
|
| either:you have dupes in one or both of the tablesor you are missing correlation column(s) in your join criteria.Post the structure of those table including the Primary Key (or unique constraint)or you can figure it out yourself by looking at each table individually. You will probably be able to see why:SELECT * FROM wparticipants where studyid = 9210SELECT * FROM Eligible where studyid = 9210Be One with the OptimizerTG |
|
|
|
johnmaio
Starting Member
9 Posts |
Posted - 2008-05-27 : 20:28:26
|
forget it...i am an idiot...there were multiple rows of data in each table...a trigger gone wrong put them there :(thanks for all the help!quote:
Originally posted by johnmaio
quote:
Originally posted by TG
>>Add distinct after Select.that would work but don't hide the problem...I'm guessing you have 2 participants with associated with each studyid.Since you aren't selecting anything from wparticipants can you remove it from the query?Be One with the OptimizerTG
I am using info from wparticipants and I still get duplicates...DISTINCT works but i don't understand why i get dups
|
|
|
|
|
|
|
query problem