CODEWITHSUNDEEP
mysqlleft-join
gregoff
gregoff

Reputation: 89

MySQL left join fill with previous value if null

Lets say I have two tables

TableA:

ID - Value1
===========
1  - A
2  - B
3  - C
4  - D

TableB:

ID - Value2
===========
1  - AA
3  - NULL
4  - DD

A normal join of these tables could be:
SELECT * FROM TableA TA LEFT JOIN TableB TB ON TA.ID = TB.ID

Result would then be:

ID - Value1 - Value2
======================
1  - A - AA
2  - B - NULL
3  - C - NULL
4  - D - DD

But, in my case I want to fill in the NULL values (where the value actually is missing) with the value from the previous row and keep the values that actually are NULL, like this:

ID - Value1 - Value2
======================
1  - A - AA
2  - B - AA
3  - C - NULL
4  - D - DD

Is there a way to accomplish this?

EDIT 1: I realised that I asked my question incorrectly... Sorry for the confusion.

Actually, I want to keep if the joined table has a value that actually is NULL, and fill the value if the value doesn't exist.

Upvotes: 3

Views: 2812

Answers (2)

sagi
sagi

Reputation: 40481

Try something like this - the inner left join is to get for each value in table b, the previous not null value (if exists) and then to join it to table a, and take value2 if not null or the new value if it is.

SELECT f.id,f.value1,coalesce(p.value2,p.PrevValue2) as value2
FROM TableA f
LEFT OUTER JOIN(SELECT t.ID,t.Value2,
                            (select s.value2 from tableB s
                             where s.value2 is not null and s.id<t.id
                             order by s.id DESC limit 1) as PrevValue2 
                FROM TableB t) p
ON(p.id = f.id)

Upvotes: 1

splash58
splash58

Reputation: 26153

SELECT TA.ID, TA.Value1, 
       if(isnull(Value2), @n, (@n:=Value2)) Value2 
    FROM TableA TA 
       LEFT JOIN 
         TableB TB 
       ON TA.ID = TB.ID 
       cross join(select @n:='') n

demo on sqlfiddle

Upvotes: 1

Related Questions