I want to display 6369 against ctr_id
2537.
Sample input data:
ctr_id | ctr_id_parent | amount |
---|---|---|
2517 | 2516 | 6369 |
2537 | 2517 | NULL |
Expected result:
ctr_id | ctr_id_parent | amount |
---|---|---|
2517 | 2516 | 6369 |
2537 | 2517 | 6369 |
So basically, when ctr_amount_consumed
is NULL
, then find the ctr_id
equivalent to ctr_id_parent
and display the value of ctr_amount_consumed
of respective ctr_id
.
I tried using fk1 table with different alias's in different joins with combination of ctr_id = ctr_id_parent and also via CTE.
WITH a AS
(
SELECT *
FROM fk1
WHERE ctr_amount_consumed IS NOT NULL
),
b AS
(
SELECT *
FROM fk1
WHERE ctr_amount_consumed IS NOT NULL
)
SELECT
a.ctr_id, b.ctr_id,
a.ctr_id_parent, b.ctr_id_parent,
a.ctr_amount_consumed, b.ctr_amount_consumed
FROM
a
CROSS join
b -- ON a.ctr_id = b.ctr_id_parent
A straight-forward sub-query when null should do the trick
select
ctr_id
, ctr_id_parent
, isnull(amount, (select amount from fk1 t2 where t2.ctr.id = t1.ctr_id_parent))
from fk1 t1