I want to display 6369 against ctr_id 2537.

Sample input data:

I want to display 6369 against ctr_id 2537.

Sample input data:

Expected result:

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

• sql-server
• t-sql

• Please tag your RDBMS - SQL Server I assume? – Dale K Commented Mar 12 at 8:37
• Do you want UNION ALL for both cases, one query for the rows with amount, one for the others? Something like this? db<>fiddle – Jonas Metzler Commented Mar 12 at 8:41
• You've probably just noticed that you can't accept both answers... so choose the one your prefer (they are both roughly equivalent). – Dale K Commented Mar 12 at 8:48
• what if also amount of id 2517 is NULL? You have to look for his parent too? maybe you need a recursive cte – MtwStark Commented Mar 12 at 13:43

2 Answers 2

You could try using COALESCE operator as given below

SELECT 
    c1.ctr_id,
    c1.ctr_id_parent,
    COALESCE(c1.amount, c2.amount) AS amount
FROM fk1 c1
LEFT JOIN fk1 c2
ON c1.ctr_id_parent = c2.ctr_id;

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