I have conditions on joining tables, how do I implement this?

Here is my problem.  I have the need for a conditional join.  I have a situation where proj_id and prop_item_key need to be joined between two tables:  DELTEK.TMP_PO_AVG_CST_PROJ    and DELTEK.TMP_PO_AVG_CST.  Problem is sometimes the proj_id will be null.  According to the rules of  NULL this can't be done because you can't compare NULL.  So when it is trying to join based on a null value, it gives me incorrect results.  

I was thinking of how I would get around this, I would run two different queries (same queries different joins based on proj_id being nULL or not null.

I don't know how to implement something like this.  I appreaciate any assitance from the experts.  If you need more info, just let me know what you need.

I hope this makes sense.  I could be going about this the wrong way.

Thanks ahead of time.
_____________________________________________________________________________________________________


If proj_id is null then


Select   CST_SRCE_TYPE_CD, TPLN.PROP_ITEM_KEY, AVGCST, TPLN.CST_SRCE_DT,  TPLN.PROJ_ID,    
MAX(CST_SRCE_ID) AS CST_SRCE_ID,
MAX(CST_SRCE_RLSE_NO) AS CST_SRCE_RLSE_NO,
MAX(CST_SRCE_LN_KEY) AS CST_SRCE_LN_KEY,    
MAX(CST_SRCE_LN_NO) AS CST_SRCE_LN_NO
                 
   
INTO     DELTEK.TMP_PO_AVG_CST_PROJ    
   
   
          from DELTEK.TMP_PO_LN as TPLN    
          LEFT OUTER JOIN DELTEK.TMP_PO_AVG_CST as TAC    
             ON TPLN.PROP_ITEM_KEY = TAC.PROP_ITEM_KEY
                   
 WHERE  CST_SRCE_DT = (SELECT MAX(CST_SRCE_DT) FROM DELTEK.TMP_PO_LN TPOLN WHERE TPOLN.CST_SRCE_DT = TPLN.CST_SRCE_DT
     AND TPOLN.PROP_ITEM_KEY = TPLN.PROP_ITEM_KEY)

AND CST_SRCE_ID  = (SELECT MAX(CST_SRCE_ID) FROM DELTEK.TMP_PO_LN TPOLN WHERE TPOLN.CST_SRCE_DT = TPLN.CST_SRCE_DT
    AND TPOLN.PROP_ITEM_KEY = TPLN.PROP_ITEM_KEY)

AND CST_SRCE_RLSE_NO  = (SELECT MAX(CST_SRCE_RLSE_NO) FROM DELTEK.TMP_PO_LN TPOLN WHERE TPOLN.CST_SRCE_DT = TPLN.CST_SRCE_DT
     AND TPOLN.PROP_ITEM_KEY = TPLN.PROP_ITEM_KEY)

AND CST_SRCE_LN_KEY  = (SELECT MAX(CST_SRCE_LN_KEY) FROM DELTEK.TMP_PO_LN TPOLN WHERE TPOLN.CST_SRCE_DT = TPLN.CST_SRCE_DT
     AND TPOLN.PROP_ITEM_KEY = TPLN.PROP_ITEM_KEY)

AND CST_SRCE_LN_NO  = (SELECT MAX(CST_SRCE_LN_NO) FROM DELTEK.TMP_PO_LN TPOLN WHERE TPOLN.CST_SRCE_DT = TPLN.CST_SRCE_DT
     AND TPOLN.PROP_ITEM_KEY = TPLN.PROP_ITEM_KEY)
   
GROUP BY  CST_SRCE_TYPE_CD, TPLN.PROP_ITEM_KEY, AVGCST, TPLN.CST_SRCE_DT,  TPLN.PROJ_ID
ORDER BY  TPLN.PROJ_ID, CST_SRCE_TYPE_CD,  TPLN.PROP_ITEM_KEY, AVGCST

else

Select   CST_SRCE_TYPE_CD, TPLN.PROP_ITEM_KEY, AVGCST, TPLN.CST_SRCE_DT,  TPLN.PROJ_ID,    
MAX(CST_SRCE_ID) AS CST_SRCE_ID,
MAX(CST_SRCE_RLSE_NO) AS CST_SRCE_RLSE_NO,
MAX(CST_SRCE_LN_KEY) AS CST_SRCE_LN_KEY,    
MAX(CST_SRCE_LN_NO) AS CST_SRCE_LN_NO
                 
   
INTO     DELTEK.TMP_PO_AVG_CST_PROJ    
   
   
          from DELTEK.TMP_PO_LN as TPLN    
          LEFT OUTER JOIN DELTEK.TMP_PO_AVG_CST as TAC    
             ON TPLN.PROP_ITEM_KEY = TAC.PROP_ITEM_KEY
 AND  
         TPLN.PROJ_ID = TAC.PROJ_ID  
                   
 WHERE  CST_SRCE_DT = (SELECT MAX(CST_SRCE_DT) FROM DELTEK.TMP_PO_LN TPOLN WHERE TPOLN.CST_SRCE_DT = TPLN.CST_SRCE_DT
     AND TPOLN.PROP_ITEM_KEY = TPLN.PROP_ITEM_KEY)

AND CST_SRCE_ID  = (SELECT MAX(CST_SRCE_ID) FROM DELTEK.TMP_PO_LN TPOLN WHERE TPOLN.CST_SRCE_DT = TPLN.CST_SRCE_DT
    AND TPOLN.PROP_ITEM_KEY = TPLN.PROP_ITEM_KEY)

AND CST_SRCE_RLSE_NO  = (SELECT MAX(CST_SRCE_RLSE_NO) FROM DELTEK.TMP_PO_LN TPOLN WHERE TPOLN.CST_SRCE_DT = TPLN.CST_SRCE_DT
     AND TPOLN.PROP_ITEM_KEY = TPLN.PROP_ITEM_KEY)

AND CST_SRCE_LN_KEY  = (SELECT MAX(CST_SRCE_LN_KEY) FROM DELTEK.TMP_PO_LN TPOLN WHERE TPOLN.CST_SRCE_DT = TPLN.CST_SRCE_DT
     AND TPOLN.PROP_ITEM_KEY = TPLN.PROP_ITEM_KEY)

AND CST_SRCE_LN_NO  = (SELECT MAX(CST_SRCE_LN_NO) FROM DELTEK.TMP_PO_LN TPOLN WHERE TPOLN.CST_SRCE_DT = TPLN.CST_SRCE_DT
     AND TPOLN.PROP_ITEM_KEY = TPLN.PROP_ITEM_KEY)
   
GROUP BY  CST_SRCE_TYPE_CD, TPLN.PROP_ITEM_KEY, AVGCST, TPLN.CST_SRCE_DT,  TPLN.PROJ_ID
ORDER BY  TPLN.PROJ_ID, CST_SRCE_TYPE_CD,  TPLN.PROP_ITEM_KEY, AVGCST