title: Is there a way to join tables using concatenated fields for the target?
body: <p>I am trying to query a series of Sybase Advantage tables from a job tracking system in order to build a report of ongoing jobs and their associated due dates.  </p>
  
  <p>This system uses a "parent/child" relationship for jobs and their subassemblies. Job data (parents) exists in the "release" table, subassembly data (children) exists in the "pnlrel" table.  Due dates are present for the jobs in the "release" table, but aren't present for subassemblies in the "pnlrel" table.  I am trying to build a query to join the tables in a manner to allow the use of the parent's due dates to represent the children in the output.  The database is Sybase Advantage 11.</p>
  
  <p>The tables and fields involved are:</p>
  
  <ol>
  <li><p>inproces - jobs that are currently in process, including parents and children.</p>
  
  <ul>
  <li>H-JOB# - The job number and release id (i.e. ABC-0100-001; job ABC-0100, release -001)
  |H-JOB#          | 
  |ABC-0100-001    |
  |ABC-0101-002    |
  |ABC-0100-001P01 |
  |ABC-0101-002P01 |</li>
  </ul></li>
  <li><p>release - all parents
  -JOB# - direct match to H-JOB#</p>
  
  <ul>
  <li>R-DUE-DATE - job's due date
  |JOB#         | R-DUE-DATE |
  |ABC-0100-001 | 01/01/2000 |
  |ABC-0101-002 | 01/01/2020 |</li>
  </ul></li>
  <li><p>pnlrel - all children</p>
  
  <ul>
  <li>P-JOB-NBR - The main job number (i.e. ABC-0100)</li>
  <li>P-REL-NBR - The job's release id (i.e. -001)
  |P-JOB-NBR |P-REL-NBR|P-REL-ID |
  |ABC-0100  |001      |P01      |
  |ABC-0100  |001      |P01      |</li>
  </ul></li>
  </ol>
  
  <p>Several other tables involved are left out of the example for clarity.</p>
  
  <p>I have tried to join two tables outright and even joined them based on a concatenation of values from one table equaling the value from a single field of another table, but am still not getting the desired output. </p>
  
  <pre><code>SELECT inproces."H-JOB#", release."R-DUE-DATE"
  FROM inproces 
  left join release on inproces."H-JOB#" = release."R-TRACKING-NBR"
  left join pnlrel on inproces."H-JOB#" = pnlrel."P-TRACKING-NBR"
  </code></pre>
  
  <p>This returns the Job Number and the Due Date for the parents, but not for the child.</p>
  
  <pre><code>|ABC-0100-001   | 01/01/2000 | (parent)
  |ABC-0100-001P01| NULL   |(child)
  |ABC-0101-002   | 01/01/2020 | (parent)
  |ABC-0101-002P01| NULL   |(child)
  </code></pre>
  
  <p>The desired result would be for the child to display the parent's due date since it has none of its own:</p>
  
  <pre><code>|ABC-0100-001    | 01/01/1901 | (parent)
  |ABC-0100-001P01 | 01/01/1901 | (child displays parent's due date)
  |ABC-0101-002    | 01/01/2020 | (parent)
  |ABC-0101-002P01 | 01/01/2020 | (child displays parent's due date)
  </code></pre>