CODEWITHSUNDEEP
sqlselectdb2left-join
Veljko
Veljko

Reputation: 1808

SQL - retrieve maximum date from other related table

I have table T1 with data:

ID GROUP
1  L1
2  L1
3  L2
4  L3

I have table T2 with data:

ID  GROUP  DATE
1   L1     03.03.2017
1   L2     01.01.2017
2   L1     03.03.2017
2   L2     02.02.2017
2   L1     01.01.2017
3   L2     03.03.2017
3   L1     02.02.2017
4   L3     05.03.2017
4   L2     02.02.2017
4   L3     05.01.2017
4   L1     01.01.2017

As result I want to get a full list from T1 (ID and current group value) but to have additional column which will be populated only if current GROUP from T1 is not L1 and in that column I want to have maximum DATE from T2 for that ID where GROUP is not L1 and it matches with GROUP value from T1.

So result of the SELECT (joined L1 and L2) should be:

ID GROUP DATE
1  L1    null
2  L1    null
3  L2    03.03.2017
4  L3    05.03.2017

I tried with this sql but it throws error:

        select t1.id,t1.group,t21.date from t1
        left outer join t2
     AS T21 on t21.id=t1.id and t1.group=t21.group
     and T21.group not in ('L1')
        AND T21.DATE IN (SELECT MAX(T22.DATE)
     FROM t2 AS T22 
WHERE T22.id=T21.id and T21.group=t22.group)

Upvotes: 0

Views: 68

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Hmmm. I think of this in terms of a correlated subquery:

select t1.*,
       (select max(t2.date)
        from t2
        where t2.id = t1.id and t1.group <> 'L1'
       ) as date
from t1;

Upvotes: 1

Amit Uppal
Amit Uppal

Reputation: 193

try something like this

Select T1.*, T2.Date1 from
T1 left outer join 
(select max(Date) as Date1
,[Group] as Group1 FROM T2
WHERE [Group]  !='L1'
group by [Group] ) T3
on t1.[GROUP] = T3.Group1

Upvotes: 0

Ofir Winegarten
Ofir Winegarten

Reputation: 9365

Wouldn't a simple join and group by be enough?

select t1.id,t1.group, max(t2.date) 
from t1 left outer join t2 on 
     t2.id=t1.id and 
     t1.group=t2.group and 
     T2.group <> 'L1'
group by t1.id,t1.group

Upvotes: 1

Related Questions