title: SQL Join based on hierarchical criteria
body: <p>I have three tables that I require to be intrajoined to form a third table. </p>
  
  <p>The first table (Basic) looks like this:</p>
  
  <pre><code>+----------+----------+---------+------------+
  | Order No | Supplier | Product |    Date    |
  +----------+----------+---------+------------+
  |      100 | SuppCo   | Shirt   | 25.09.2014 |
  |      101 | SuppCo   | Pants   | 25.09.2014 |
  |      102 | SuppCo   | Shirt   | 25.09.2014 |
  +----------+----------+---------+------------+
  </code></pre>
  
  <p>The second table (Color) looks like this:</p>
  
  <pre><code>+------+--------+
  |  ID  | Color  |
  +------+--------+
  | 1ABC | yellow |
  | 1ABD | red    |
  | 1ABE | pink   |
  | 1ABF | orange |
  | 1ABG | teal   |
  | 2DEG | green  |
  | 3HIK | blue   |
  +------+--------+
  </code></pre>
  
  <p>The third table (Detail) looks like this:</p>
  
  <pre><code>+------+----------+-----------+
  |  ID  | Order No | Order Ext |
  +------+----------+-----------+
  | 1ABC |      100 | -         |
  | 1ABD |      100 | Prime     |
  | 1ABE |      100 | Basic1    |
  | 1ABF |      100 | Basic2    |
  | 1ABG |      100 | Basic3    |
  +------+----------+-----------+
  </code></pre>
  
  <p>The final table should look like this:</p>
  
  <pre><code>+----------+------+-------+------------+
  | Order No |  ID  | Color |    Date    |
  +----------+------+-------+------------+
  |      100 | 1ABD | red   | 25.09.2014 |
  |      101 | 2DEG | green | 25.09.2014 |
  |      102 | 3HIK | blue  | 25.09.2014 |
  +----------+------+-------+------------+
  </code></pre>
  
  <p>The table is based on the <code>Basic</code> table, where I use Order No and Date.
  From the order number, I want to get the ID from the <code>Detail</code> table.</p>
  
  <p>The join between <code>Basic</code> and <code>Detail</code> should have the following criteria:
  Join the same order number and get the ID where <code>Order Ext = 'Prime'</code>, if Prime does not exist as an extension, then <code>Order Ext = '-'</code> and if that does not exist, then take any of the rest (it does not matter at this point).
  After this, the ID should be taken and get the right row from the <code>Color</code> table.</p>
  
  <p>I was thinking about Row Over but that is quite resource draining, so I was thinking if there was a more efficient way of doing this?</p>