w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Select rows in one table, adding column where MAX(Date) of rows in other, related table

If we assume that tasks only become more complete, you can do this with a left outer join and aggregation:

select t.ID, t.Name, coalesce(max(n.complete), 0)
from tasks t left outer join
     notes n
     on t.id = n.id_task
group by t.id, t.name

If tasks can become "less complete" then you want the one with the last date. For this, you can use row_number():

select t.ID, t.Name, coalesce(n.complete, 0)
from tasks t left outer join
     (select n.*, row_number() over (partition by id_task order by date
desc) as seqnum
      from notes n
     ) n
     on t.id = n.id_task and n.seqnum = 1;

In this case, you don't need a group by, because the seqnum = 1 performs the same role.





© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.