How do I remove Duped records in Stored Procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

How do I remove Duped records in Stored Procedure

Author

Topic

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-08-27 : 19:03:57

Hi Everyone,
I have a ASP.Net Project which I am doing a lookup from a country table. The customer table has a 1 to many relationship to the country table. I used Select Distinct in my stored procedure to eliminate my duplicate records but they still appear in my ASP.Net DropDownList. My Stored procedure looks like this ...

Create Procedure [sp_GetCountries] as
Select Distinct bk_country_id.bk_countryname
From dbo.Tbk_country
Order by bk_countryname ASC
Go

Please help :)
thx,
Ed

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-27 : 19:07:08

If you run the query inside Query Analyzer, do you see the duplicates? Also, I don't even understand how this stored procedure is working. The column should just say bk_countryname and not bk_country_id.bk_countryname. If you are going to use the two part naming convention, then it is tablename.columnname. So it would be tbk_country.bk_countryname.

Also, NEVER start any name of any object with sp_. SQL Server does not check the current database first to see if it exists there, it checks the master database first. usp_ is what a lot of people use (user stored procedure is what it stands for).

Tara

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-08-27 : 19:20:33

Thanks Tara for your input. Yes the records do show up as duplicates. The reason being is because there are more than 1 customer in the US and I am building my first Relational database. I have 4 tables above country with the first table as a 1 to many relation with the next table and then steps down to the country table. Please see my raw diagram below.

User table - Has Username and Passwords to the next two tables
BD_Managers - Managers which this table links to customers table
Program_Managers - Program Managers which this table links to customers table
Customers - Customers which links to Countries table
Countries - Table consisting of places where managers and customers are related to the country.

This is why my Countries table has many records with USA in it.

I have not built relational databases before but I have 2 oracle DBAs that told me the mapping of the tables is correct. Each table has a unique ID. Maybe I should work with a string between IDs. I don't know.

Appreciate your input :)
thx,
Ed

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-27 : 19:27:48

Have you provided the entire query? Your query should not return any duplicates. Take this example for instance:


SET NOCOUNT ON

CREATE TABLE Table1
(
Column1 CHAR(1) NOT NULL
)

INSERT INTO Table1 VALUES('B')
INSERT INTO Table1 VALUES('B')
INSERT INTO Table1 VALUES('B')
INSERT INTO Table1 VALUES('A')
INSERT INTO Table1 VALUES('C')

SELECT * FROM Table1

SELECT DISTINCT Column1
FROM Table1
ORDER BY Column1

DROP TABLE Table1

The first result set shows what data is in the table. The second result set shows the distinct records. Notice how we only get one B.

Also, it would be helpful to see the DDL for the tables and sample data. Both of these should be in the same form as my example. This helps get a very fast response from us.

Tara

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-28 : 02:45:36

quote:
Originally posted by edb2003

Thanks Tara for your input. Yes the records do show up as duplicates. The reason being is because there are more than 1 customer in the US and I am building my first Relational database. I have 4 tables above country with the first table as a 1 to many relation with the next table and then steps down to the country table. Please see my raw diagram below.

User table - Has Username and Passwords to the next two tables
BD_Managers - Managers which this table links to customers table
Program_Managers - Program Managers which this table links to customers table
Customers - Customers which links to Countries table
Countries - Table consisting of places where managers and customers are related to the country.

This is why my Countries table has many records with USA in it.

I have not built relational databases before but I have 2 oracle DBAs that told me the mapping of the tables is correct. Each table has a unique ID. Maybe I should work with a string between IDs. I don't know.

Appreciate your input :)
thx,
Ed

Ed, I dont see how you could have more than one record in the Countries table for the same country. If you have a one-to-many relationship between Customers and Countries, eventually the Customers table will have multiple customer records for customers living in a country. So if you were to run a query like :

SELECT CountryID FROM Customers

you'd probably see plenty of duplicates. But this query should return no duplicates (at least not because of the one-to-many relation between customers and countries, maybe due to bad data perhaps):

SELECT CountryID FROM Countries

Is tbk_Country a table or a view? You might have a view like this:

SELECT CustomerID, CustomerName, Customerblah, CountryID, CountryName
FROM Customers INNER JOIN Countries
ON Customers.CountryID = Countries.CountryID

Now if you run the second query on a view with a definition similar to the one above, you will see duplicates.

Owais

Make it idiot proof and someone will make a better idiot

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-08-28 : 11:27:00

Create Procedure [sp_GetCountries] as
Select Distinct bk_country_id.bk_countryname
From dbo.Tbk_country
Order by bk_countryname ASC
Go

just a spelling mistake ?

Voted best SQL forum nickname...."Tutorial-D"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-28 : 12:27:51

I believe that Ed hasn't provided the entire stored procedure because that SELECT statement has syntax problems in it. He is selecting bk_country_id.bk_countryname, but in the FROM, he is using Tbk_country. I suspect that there is a join that is missing from the query.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources