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  SQL Server Development (2000)  Creating a parent-child like table

Author  Topic  Twiki Starting Member 1 Post Posted - 2009-01-22 : 09:18:29 Hi all, I'm new here and have not been working with SQLserver long. I have been looking at this forum and Google for a solution to my problem, but I cannot find it. I have a table with patient information. In this table patients can be linked to each other. This happens when the patient was registered, but it was found out later that the patient was already in the system. Some patients have more than 10 patientnumbers! The patient table is basically something like this: Patientid, linked patientid, patientlink sign. Not all patientid have a linked patientid (it is obvious that they only have a linked id when another patientid is linked to them). When they do have a linked patientid, the patientlink sign field is filled with either an '*' or an '>'. If '*' is filled, this means, the patientid is the active id (there can only be one active id for each patient). If an '>' is filled, it means that the patientid is inactive. Now, the big problem is that the ids are linked to each other in a circle. So patientid 1 is linked to 2, 2 is linked to 3, 3 to 4 and 4 is linked to 1. No linked patientid is used more than once. I need to create a table in which I can make a direct link from each linked patientid to the active patientid. So something like, activeid, subid. If 1 is the active id, I need something like: Activeid Patientid 1 2 1 3 1 4 5 6 5 7 8 <null> Can anybody help me? Thanks! Martha