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 |
|
smh6n
Starting Member
3 Posts |
 Posted - 2008-08-20 : 15:25:49
|
| I have two tables, one containing people and a second table, used when applicable, that relates records in the first table.Table One - EntityEntityID LastName FirstName1 Jones Sam2 Jones Susan3 Smith Jane4 Thomas Fran5 Thomas Phil6 Doe JuniorTable Two - LinkLinkID EntityID1 EntityID2 Relationship1 Relationshiop2 1 1 2 Husband Wife2 4 5 Wife Husband3 4 6 Mother SonI am trying to get a count of family units which in the example above would be1 Sam & Susan Jones1 Jane Smith1 Fran, Phil Thomas and Junior Doe---3 TotalAll I can think to do is use cursors which I've never used before or create a looping dts package. Any ideas are appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
smh6n
Starting Member
3 Posts |
Posted - 2008-08-20 : 16:14:42
|
| DB is in production so it would be difficult to change the table design. What did you have in mind? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-20 : 16:16:51
|
| The way that I see it is you'd break it (Table Two - Link) out to two tables. The first table would contain every possible relationship type. The second table would contain the links between your entities and my new table. This way you could do simple joins to get the data returned.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
Related records in tables