title: SQL to Select next available code from table
body: <p>I would like to Select an available PROMO_CODE (table B) for the CUST_ID records (table A) that do not have one.
  I think I have the base SQL for each table, but not sure how to combine these for an output.</p>
  
  <p>-----Table A -----</p>
  
  <pre><code>CUST_ID|CREATED_DATE|MY_CODE
  XYZ111|1/1/2015|5
  XYZ222|1/1/2015|6
  XYZ333|7/12/2018|   
  XYZ444|7/13/2018|   
  XYZ555|7/14/2018|   
  XYZ666|2/2/2012|3
  XYZ777|2/2/2012|2
  XYZ888|7/15/2018|   
  XYZ999|6/6/2016|4
  XYZ1110|6/6/2016|1
  </code></pre>
  
  <p>-----Table B -----</p>
  
  <pre><code>PROMO_CODE|PROMO_CODE_STATUS
  1|assigned
  2|used
  3|
  4|used
  5|assigned
  6|
  7|
  8|assigned
  9|assigned
  10|
  </code></pre>
  
  <p>----Desired Output-----</p>
  
  <pre><code>CUST_ID|MY_NEW_CODE
  XYZ333|3
  XYZ444|6
  XYZ555|7
  XYZ888|10
  </code></pre>
  
  <p>----What I have so far but NOT connected-----</p>
  
  <pre><code>Select a.CUST_ID, b.PROMO_CODE as MY_NEW_CODE  
  
  (Select a.CUST_ID, a.CREATED_DATE, a.MY_CODE
  from $A$ a where 
  a.MY_CODE is NULL AND trunc(a.CREATED_DATE) &gt;= trunc(sysdate -1))
  
  (Select b.PROMO_CODE, b.PROMO_CODE_STATUS
  from $B$ b where 
  b.PROMO_CODE_STATUS is NULL)
  </code></pre>