title: Returning linked records and avoiding multiple subqueries/loops
body: <p>I am facing a new SQL database scenario (for me) and I would really appreciate any advice. I am sure  there is a really simple answer, but I can't quite get my head around it. I will try and be as specific as possible...</p>
  
  <p>Client will have many seperate appts in our DB. These appts can be one-off or linked as a series of visits. I am trying to find a specific Appointment event and find all the linked appointments to this event, out of my Microsoft SQL database 2008 R2 DB. I would ideally like to see each linked appt as a separate row in the query results. </p>
  
  <p>All Appts details are in table 1 (lets call it "Appt"). 
  The links between the Appt and <strong><em>its previous Appt only</em></strong> are in Table 2 ("ApptLink"). The ApptLink table includes the ApptId as a FK and the previous linked ApptId (FK)in a sperate column.</p>
  
  <p>Eg. Sample Data</p>
  
  <p>Appt Table</p>
  
  <p>ApptId (PK), DateTime, eventtype, duration, comment, apptstatus, location</p>
  
  <p>ApptLink table</p>
  
  <p>PK, ApptId (FK), PreviousApptId (FK)</p>
  
  <p>I'll try and put it another way. I can return the main appt I am looking for no problems, but when I want to find the previous appt, I need to join the Apptlink table which will show me the previous ApptId only, and to find the previous appt to that I need to find that Id in the apptlink table and its previousapptid and so on... I guess I need to run a Subquery/loop to return the results of that appt as I might have <strong><em>30 linked appts</em></strong>.</p>
  
  <p>I started creating something like below, but I know its not right and there must be a simpler/quicker way than running 30 sub queries. I am feeling like I need to create heaps of sub queries to make sure I get all the previousapptIds, until I return a null, which mean there are no more linked previousapptIds.</p>
  
  <pre><code>Select *
  From Appt 
  Where ApptId in (Select Appt.ApptId, ApptLink.PreviousApptId
                   From Appt 
                   LEFT OUTER JOIN ApptLink ON Appt.ApptId=ApptLink.ApptId
                   Where Appt.Apptevent = 'Finish' and Appt.DateTime = '2014-3-1'
                   UNOIN
                   Select ApptLink2.PreviousApptId
                   From AppLink ApptLink2
                   Where ApptLink2.ApptId = ApptLink.PreviousApptId --(from above?)
                   UNOIN
                   Select ApptLink3.PreviousApptId
                   From AppLink ApptLink3
                   Where ApptLink3.ApptId = ApptLink2.PreviousApptId --(from above?)
  
                   --and so on for 30+ times...
  
                   )
  </code></pre>
  
  <p>Truly appreciate your assistance.</p>
  
  <p>Josh.</p>