title: Select from another table based on condition
body: <p>I am new to SQL Server and SQL queries. These are my three tables:</p>
  
  <p><a href="https://i.stack.imgur.com/UOBGJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UOBGJ.png" alt="enter image description here"></a></p>
  
  <p><strong>Primary keys</strong></p>
  
  <ul>
  <li>Table 1: CaseId</li>
  <li>Table 2: ID</li>
  <li>Table 3: parentId</li>
  </ul>
  
  <p>In table 3 - if the parent is a father then the status code is <strong>8</strong> and if the parent is a mother then the status code is <strong>10</strong></p>
  
  <p>I want to write a select query where I can get an output like the following:</p>
  
  <p><a href="https://i.stack.imgur.com/vxjfA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vxjfA.png" alt="enter image description here"></a></p>
  
  <p>Please help me, thanks in advance</p>