Avoid "duplicates" - SQL Server Forums

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

Avoid "duplicates"

Author

Topic

axydias
Starting Member

1 Post

Posted - 2007-01-01 : 11:41:07

I have a table named "AUTHOR" with columns (name, citizenship, birth_year). I want to select all available pairs of authors (e.g. author1 author2) WITHOUT the reversed pairs (e.g. author2 author1). I am allowed only to use select (maybe a select is in an other select command).

Can anyone help me please??

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-01 : 13:33:50

Sounds lke homework?

You will need to join the Author table to itself then just select the pairs where the name from the first table is greater than that from the second.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-01 : 14:22:17

Homework on the first day of the new year?
Poor guy...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-01 : 17:32:02

SELECT t1.Name, t2.Name
FROM Authors AS t1
CROSS JOIN Authors AS t2
WHERE t1.Name < t2.Name

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-01 : 18:55:14

You don't need a cross join but it does the same thing
SELECT t1.Name, t2.Name
FROM Authors t1
JOIN Authors t2
on t1.Name < t2.Name

Probably a cross join is more descriptive but the inner join is less typing.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources