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