SQL to Select next available code from table

Question

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.

-----Table A -----

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

-----Table B -----

PROMO_CODE|PROMO_CODE_STATUS
1|assigned
2|used
3|
4|used
5|assigned
6|
7|
8|assigned
9|assigned
10|

----Desired Output-----

CUST_ID|MY_NEW_CODE
XYZ333|3
XYZ444|6
XYZ555|7
XYZ888|10

----What I have so far but NOT connected-----

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) >= trunc(sysdate -1))

(Select b.PROMO_CODE, b.PROMO_CODE_STATUS
from $B$ b where 
b.PROMO_CODE_STATUS is NULL)

Answer 1

I would think in something like this:

Select a.CUST_ID, b.PROMO_CODE as MY_NEW_CODE  
FROM $A$ a, $B$ b
WHERE a.MY_CODE = b.PROMO_CODE 
AND trunc(a.CREATED_DATE) >= trunc(sysdate -1)
AND b.PROMO_CODE_STATUS is NULL

Answer 2

I ended up using ROW_NUMBER() on each table and that works:

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY a.CUST_ID ASC) AS Row_CUST_ID, a.CUST_ID, a.MY_NEW_CODE FROM $A$ a where a.MY_NEW_CODE is NULL AND trunc(a.LOADED_DATE) >= trunc(sysdate -1)) c INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY b.PROMO_CODE ASC) AS Row_PROMO_CODE, b.PROMO_CODE, b.PROMO_CODE_STATUS FROM $B$ b where b.PROMO_CODE_STATUS is NULL) p ON c.Row_CUST_ID = p.Row_PROMO_CODE

---

It produces the expected result:

ROW_CUST_ID|CUST_ID|MY_NEW_CODE|ROW_PROMO_CODE|PROMO_CODE|PROMO_CODE_STATUS

1 | 111 | | 1 | BBB |
2 | 222 | | 2 | DDD |
3 | 333 | | 3 | FFF |

From updated tables:

-----table A

CUST_ID | MY_NEW_CODE | LOADED_DATE

111 | | 2018-07-18 00:00:00.0
222 | | 2018-07-18 00:00:00.0
333 | | 2018-07-19 00:00:00.0
444 | | 2018-07-12 00:00:00.0

-----table B

PROMO_CODE | PROMO_CODE_STATUS

AAA | used
BBB |
CCC | assigned
DDD |
EEE | assigned
FFF |