Join givng duplicate rows that dont exist

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

Join givng duplicate rows that dont exist

Author

Topic

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 2013-01-11 : 21:41:14

hi,
I tried this join shown below but it gave me three rows in the result set but actually i only have one row in the table for the given ID, please help me underdstand this and how to avoid this

SELECT DISTINCT CTZ.[Territory_Zip_ID]
,CTZ.[Zip_Code]
,CTZ.[Zip_Code_Extension_From]
,CTZ.[Zip_Code_Extension_To]
,ZC.City_Town
,SP.State_Name AS State_Province
,CO.Country_Name AS Country
FROM TERRITORY_ZIPCODE CTZ
Inner Join ZIP ZC On (CTZ.Zip_Code = ZC.Zip_Code)
Inner Join STATE SP on (SP.State_Code = ZC.State_Province)
Inner Join COUNTRY CO on (CO.Country_ID = SP.Country_ID)
WHERE District_Territory_Id = @pTerritory_Id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-12 : 03:21:34

thats because DISTINCT gives you distinct set of values for all the column combinations. So it may be that one of involved column has unique columns.

try this


SELECT *
FROM
(
SELECT  ROW_NUMBER() OVER (PARTITION BY CTZ.[Territory_Zip_ID] ORDER BY CTZ.[Territory_Zip_ID]) AS Seq,
,CTZ.[Territory_Zip_ID]
,CTZ.[Zip_Code]
,CTZ.[Zip_Code_Extension_From]
,CTZ.[Zip_Code_Extension_To]
,ZC.City_Town
,SP.State_Name AS State_Province
,CO.Country_Name AS Country
FROM TERRITORY_ZIPCODE CTZ
Inner Join ZIP ZC On (CTZ.Zip_Code = ZC.Zip_Code)
Inner Join STATE SP on (SP.State_Code = ZC.State_Province)
Inner Join COUNTRY CO on (CO.Country_ID = SP.Country_ID)
WHERE District_Territory_Id = @pTerritory_Id
)t
WHERE Seq=1

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

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-01-12 : 11:22:42

I'm betting that ZipCode isn't unique.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 2013-01-12 : 14:21:17

Thanks,
I will try that and let you know if it solved .

quote:
Originally posted by Jeff Moden

I'm betting that ZipCode isn't unique.

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources