SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join givng duplicate rows that dont exist
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 01/11/2013 :  21:41:14  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/12/2013 :  03:21:34  Show Profile  Reply with Quote
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/

Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/12/2013 :  11:22:42  Show Profile  Reply with Quote
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."
Go to Top of Page

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 01/12/2013 :  14:21:17  Show Profile  Reply with Quote

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."

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000