sql-servert-sql

Get value from first row to another based on different primary key values


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

Solution

  • 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