DISTINCT to eliminate duplicates in JOIN

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

Transact-SQL (2000)

DISTINCT to eliminate duplicates in JOIN

Author

Topic

gjerstad
Starting Member

1 Post

Posted - 2003-09-08 : 12:13:27

Fellas,

I'm a beginner using MS SQL 2000 wondering how the hell JOIN works. The code wotks fine, I just need some theory!

The code below required that I add DISTINCT on my web server because it would return duplicate rows. My localserver didn't require DISTINCT alltogether.

SELECT DISTINCT Title, C.[Name] AS Category
FROM News_Stories AS N LEFT OUTER JOIN News_Categories AS C
ON N.CategoryID = C.CategoryID

So I'm wondering how does the join ever duplicate the rows in the first place? What is the process' steps to return dups?

Dupped,

Kim in Dr Congo

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-08 : 12:46:01

It's a relational thing...

I would think that a news category may have 1 to many news stories..

If it's possible that a title is not defined as the primary key of new stories, you would then allow "duplicates". But are they really?

For example, check out:



USE Northwind
Go

SELECT * FROM Orders
WHERE OrderId = 10248

SELECT * FROM Orders l INNER JOIN [Order Details] r ON l.OrderId = r.OrderId
WHERE l.OrderId = 10248
GO

In the second query it appears there are duplcates, but they are the parental rows related to the details...

1 to many...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources