title: sql join within join?
body: <p>I need your help building a SQL statement I can't wrap my head around.</p>
  
  <p>In a database, I have four tables - files, folders, folders_files and links.</p>
  
  <p>I have many files. One of them is called "myFile.txt".</p>
  
  <p>I have many folders. "myFile.txt" is in some of them. The first folder it appears in is called "firstFolder".</p>
  
  <p>I have many links to many folders. The first link to "firstFolder" is called "firstLink".</p>
  
  <p>The data structure for the example would be:</p>
  
  <pre><code>// files
  Id: 10
  Name: "myFile.txt"
  
  // folders
  Id: 20
  Name: "firstFolder"
  
  // folder_files (join table)
  Id: 30
  Folder_Id: 20 (meaning "firstFolder")
  File_Id: 1 (meaning "myFile.txt")
  
  // links
  Id: 40
  Name: "firstLink"
  Folder_Id: 20 (meaning "firstFolder")
  </code></pre>
  
  <p>FIRST QUESTION: How do I get the record for "myFile.txt" AND the Name and Id of "firstLink" (the first link), querying on file Id = 10, based on the lowest Id of the folder and the link?</p>
  
  <p>SECOND QUESTION: How do I get the record for "myFile.txt" AND the Name and Id of "firstLink" (the first link), querying on all files, based on the lowest Id of the folder and the link?</p>
  
  <p>put another way - how do I get the first link to the first folder containing "myFile.txt"? </p>
  
  <p>Resulting in a record that looks like:</p>
  
  <pre><code>Id: 10
  Name: "myFile.txt"
  LinkId: 40
  LinkName: "firstLink"
  </code></pre>
  
  <p>Thanks!</p>