title: How conditional join if first matching has no row, use second matching
body: <p>My client table has some special rows that work this way. There are multiple customer records that belongs to the same customer but with slightly different column value. The tables belong to them and I can't change anything in their table, so I have to work on a script to deal with this.</p>
  
  <p>Now I need to compare customer data from main table and get the data from client table. The condition is as below:</p>
  
  <ol>
  <li><p>If the main table Id exists in client table, get the only record matching with main table Id column</p></li>
  <li><p>If the main table Id not exists in client table, use main table Idnumber to find and match Idnumber in client table. </p></li>
  </ol>
  
  <p>Below is an example of what I'm trying to achieve:</p>
  
  <p>Let's say this data exist in the main table and client table below:</p>
  
  <p><a href="https://i.stack.imgur.com/0ju23.png" rel="noreferrer"><img src="https://i.stack.imgur.com/0ju23.png" alt="enter image description here"></a></p>
  
  <p>In the scenario above, my script should always pick the client table PKId 1 only and ignore the row PKId 2 in client table by matching the main table Id column and client table ClientId column. </p>
  
  <p>And for another scenario below:</p>
  
  <p><a href="https://i.stack.imgur.com/WE9Sx.png" rel="noreferrer"><img src="https://i.stack.imgur.com/WE9Sx.png" alt="enter image description here"></a></p>
  
  <p>Since the row PKId 1 has empty ClientId and there's no way to match the client Id 10 in client table, my script should use main table Idnumber to find and match record in the client table and would pick up the row PKId 2 by the Idnumber column.</p>
  
  <p>I wanted to do a case in the join condition but not sure how I should construct it. I'm thinking about something like below (not actual SQL statement, just some idea):</p>
  
  <pre><code>Select 
      c.Id, c.Name, c.AuthorizeToken 
  From 
      Client c 
  Left Join 
      Main m on (If m.Id = c.ClientId has data return, get AuthorizeToken from that row only;
   else if m.Id = c.ClientId has no data return, use m.Idnumber = c.Idnumber     
   to find and get AuthorizeToken) 
  </code></pre>
  
  <p>Appreciate if can advice me on any alternative to achieve this. </p>