remove multiple rows - 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

SQL Server 2005 Forums

Transact-SQL (2005)

remove multiple rows

Author

Topic

amitbarkai
Starting Member

19 Posts

Posted - 2010-06-23 : 03:20:21

Hey All

i have a simple question
let say my table looks like this

Persons

ID FName LName SpouseID
60870335 aaa bbb 00235448
02547855 sda eee NULL
00235448 vvv bbb 00235448

as you can see person with ID 60870335 is married to 00235448
what i would like to do is get the married cupples ones
meaning

ID Fname LName ID FName LName
00235448 aaa bbb 00235448 vvv bbb

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-23 : 03:33:20

[code]
select p.ID, p.Fname, p.LName, p.SpauseID, s.FName, s.LName
from Person p
inner join Person s on p.SpauseID = s.ID
where p.SpauseID is not null
and p.ID <> p.SpauseID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

amitbarkai
Starting Member

19 Posts

Posted - 2010-06-23 : 03:51:50

you anser will replay 2 rows
ID Fname LName ID FName LName
60870335 aaa bbb 00235448 vvv bbb
00235448 vvv bbb 60870335 aaa bbb

i want to remove these duplication

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-23 : 04:27:11

quote:
Originally posted by amitbarkai

you anser will replay 2 rows
ID Fname LName ID FName LName
60870335 aaa bbb 00235448 vvv bbb
00235448 vvv bbb 60870335 aaa bbb

i want to remove these duplication

not with the sample data that you posted in your first post.

KH
[spoiler]Time is always against us[/spoiler]

amitbarkai
Starting Member

19 Posts

Posted - 2010-06-23 : 04:40:41

ok

is there a way to return only one row ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources