Outer Apply successer cell chek and get the result, Sql Server

Outer Apply successer cell chek and get the result

create table Las(name nvarchar(50), Depth int);
insert into Las values('testName1', 25);
insert into Las values('testName2', 76);
insert into Las values('testName3', 31);
insert into Las values('testName4', 24);

create table Calci(name nvarchar(50), Depth int, carbon int, calcium int);
insert into Calci values('testName1', 24,30,10);
insert into Calci values('testName1', 30,25,15);
insert into Calci values('testName1', 31,24,16);
insert into Calci values('testName2', 75,25,15);
insert into Calci values('testName2', 80,24,16);
insert into Calci values('testName2', 85,28,25);

create table Drill(name nvarchar(50), Depth int, AHD int, PHD int);
insert into Drill values('testName1', 24,15,17);
insert into Drill values('testName1', 30,99,88);
insert into Drill values('testName1', 31,24,16);
insert into Drill values('testName2', 75,25,15);
insert into Drill values('testName2', 80,30,60);
insert into Drill values('testName2', 85,28,25);
insert into Drill values('testName3', 30,90,80);
insert into Drill values('testName3', 35,66,77);

select * from Las
select * from Calci
select * from Drill

select t1.name,t1.Depth,q.carbon,q.calcium, D.AHD, D.PHD
from Las t1
OUTER apply
(
    select top 1 carbon,calcium
    from Calci t2
    where 
        t2.name=t1.name
        and t2.Depth>t1.Depth -- From all the highest,....
    order by t2.Depth asc -- get the smaller
)q
OUTER apply
(
    select top 1 AHD,PHD
    from Drill t2
    where 
        t2.name=t1.name
        and t2.Depth>t1.Depth -- From all the highest,....
    order by t2.Depth asc -- get the smaller
)D


run \| edit \| history \| help	0