title: SQL - Multiple table join and select first record on conditions
body: <p>I Have the following tables:</p>
  
  <pre><code>Projects (ID, Name, ManagerUser_ID)
  Users(ID, Name, Active)
  Delegates(ProjectID, UserID, OrderNo)
  </code></pre>
  
  <p>The <code>ManagerUser_ID</code> is the Project Manager for the project and is a link to the users table. Users, however can be <code>Inactive</code>. So, the Delegates table is a many to many table defining Users that can access the project data. </p>
  
  <p>What i require, is to select the first Delegate for a project that is <code>Active</code> assuming the manager is <code>Inactive</code>. The <code>OrderNo</code> field specifies the order of the delegates (1 being first).</p>
  
  <p>Some sample data:</p>
  
  <pre><code>Project
  1, Project1, 2
  2, Project2, 4
  3, Project3, 1
  
  Users
  1, Joe, true
  2, John, false
  3, Dave, true
  4, Bob, false
  
  Delegates
  1, 4, 1
  1, 1, 2
  1, 3, 3
  2, 2, 1
  2, 4, 2
  2, 3, 3
  </code></pre>
  
  <p>So the output of my query would need to show:</p>
  
  <pre><code>Project1, Joe
  Project2, Dave
  Project3, Joe
  </code></pre>
  
  <p>Show Projects and Users for each project where if the <code>ManagerUser_ID</code> is inactive, then Select the <code>User</code> in <code>Delegates</code> with the lowest <code>OrderNo</code> that is <code>Active</code>.</p>