How to join two tables with different status


#1

I have two table the first one is with 3 columns As

CR_GENERAL:

  CRNO, | mainCR | CR status  
 -------|--------|------------ 
    555 | Null   | not active 
   5647 | 555    | active     
   7845 | 555    | not active 
    434 | 566    | active     
    566 | null   | active     
   6758 | null   | not active 

Second table (IDENTIFICATIONS):

  mainCr | partner 
 --------|--------- 
  555    | 23453

In this case in the first table when ever the MainCR is null then the CRNO is the MAINCR and the other thing when ever the CRNO is active the mainCR need to be active as well even if it is not active in this case

555 should be active because 5647 is active

then I need to join with the second table with the main only with Partner

so I only need

555 and 566 IDs because they are active

I used this query and I am not sure if it is the correct one

select distinct m.MAIN_CR ,R.PARTNER

from (select 
   case
      when  maincr ='NULL' then crno  
       else  maincr 
        end  MAIN_CR,crstatus,CRNO
from CR_GENERAL ) M inner Join
IDENTIFICATIONS R
  ON M.CRNO = R.ID_NUMBER
where M.crstatus ='ACTIVE' 

and I used this query as well and I got different results

SELECT DISTINCT COALESCE(C.maincr, C.crno) AS mainCr, 
                I.partner 
FROM   GDM.DWH_MCI_ALL_CR_GENERAL  c 
       INNER JOIN DWH.DWH_BP_IDENTIFICATIONS I
               ON i.ID_number = COALESCE(C.maincr, C.crno) 
               where  (c.crstatus ='ACTIVE' 
  mainCr | partner 
 --------|--------- 
     555 |   23453 
     566 |  675567 

#2

Well other than that you include a field in your identification table that you havent declared (R.CURRENT_FLG)…

was there a question here?