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.
Author |
Topic |
newnoise
Starting Member
3 Posts |
Posted - 2013-05-29 : 18:10:51
|
I have a database that contains unique IDs in one column and a list of spouse IDs in a separate column. The problem is that these spouse IDs also appear somewhere in the column with unique IDs (i.e., each spouse also has his/her own unique ID). I need code that will look at the values in the spouse ID column and make sure that only 1 spouse from every married couple is retained in the list. Here's a sample of the data:UniqueID SpouseID1 22 13 NULL4 155 NULL6 97 298 NULL9 610 NULL In the example above, unique ID 1 is married to unique ID 2, unique ID 6 is married to unique ID 9, and unique IDs 4 and 7 are married to people whose unique IDs do not appear in this list of 10. In addition, there are four IDs in the above example that are unmarried (or whose spouses are not in the database).Any idea of how I can get the above list of 10 to be the following:UniqueID SpouseID1 23 NULL4 155 NULL6 97 298 NULL10 NULL ...where unique IDs 2 and 9 have been removed because their spouse is already in the list? (Alternatively, the code could remove unique IDs 1 and 6, just as long as only one spouse per couple remains in the list.)Any help would be greatly appreciated. Thanks!SQL Server 2012 |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-29 : 22:04:59
|
[CODE]DECLARE @TEMP1 TABLE(uniqueID INT, SpouseID INT)INSERT INTO @TEMP1(uniqueID, SpouseID) VALUES(1, 2),(2, 1),(3, NULL),(4, 15),(5, NULL),(6, 9),(7, 29),(8, NULL),(9, 6),(10, NULL);SELECT * FROM @TEMP1 T1 where uniqueID NOT IN (SELECT COALESCE(T2.SpouseID, 0) from @TEMP1 T2 where T2.UniqueID <= T1.UniqueID)[/CODE] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-30 : 01:01:16
|
[code]SELECT * FROM @TEMP1 t1 where NOT EXISTS (SELECT 1 FROM @TEMP WHERE UniqueID = t1.SpouseID AND SpouseID = t1.UniqueID )OR UniqueID < SpouseID[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|