sql server - SQL optimize inner join (Inner join with union and possible use of subquery) -

i'm having 2 tables -- test, child contains versioned data. description columns:

test, child table id - record id (not unique due multiple versions) modstamp - timestamp @ record inserted deleteddate - timestamp @ record deleted; null value means record not deleted yet. composite key = {id, modstamp}  child table data - foreign key references test(id) data2 - foreign key references test(id) 

i working on requirement records selected between 2 timestamps - $fromtime , $totime. able select records on test table running self-join.

select v.id, v.modstamp test v inner join     (select id, max(modstamp) maxdatetime     test modstamp >= '2017-08-16 15:08:04 +00:00' , modstamp <= '2017-08-16 17:08:04 +00:00' group id) g  on v.id = g.id  , v.modstamp = g.maxdatetime v.deleteddate null 

i required select latest version of corresponding child records (without duplicate record id , group clause) parent records selected. application holds results above query , runs following query select valid child record every value ['aaa', 'bbb']:

 select v.* child v     inner join         (select id, max(modstamp) maxdatetime         child (data = ? or data null) , modstamp <= '2017-08-16 17:08:04 +00:00' group id             union         select id, max(modstamp) maxdatetime         child (data2 = ? or data2 null) , modstamp <= '2017-08-16 17:08:04 +00:00' group id         ) g      on v.id = g.id      , v.modstamp = g.maxdatetime 

can suggest how optimize solution? current approach, if n parent records selected, there n inner joins running.

this query intended join throws 'multi-part identifier not found error'. other ways rewrite query? -- intended query

select v.* child v     inner join (select distinct(id) data) d     on v.id = d.id     inner join         (select id, max(modstamp) maxdatetime         child (data = d.id or data null) , modstamp <= '2017-08-16 17:08:04 +00:00' group id             union         select id, max(modstamp) maxdatetime         child (data2 = d.id or data2 null) , modstamp <= '2017-08-16 17:08:04 +00:00' group id         ) g      on v.id = g.id      , v.modstamp = g.maxdatetime; 

sample info run script:

test | id | modstamp | deleteddate |  child | id | data | data2 | modstamp | deleteddate | 

script (using sql server):

drop table test; drop table child;  create table test(id varchar(20), modstamp datetimeoffset, deleteddate datetimeoffset); insert test values('aaa', '2017-08-16 15:08:04 +00:00', null); insert test values('aaa', '2017-08-16 16:08:04 +00:00', null); insert test values('aaa', '2017-08-16 17:08:04 +00:00', null); insert test values('aaa', '2017-08-16 18:08:04 +00:00', '2017-08-16 18:08:04 +00:00'); insert test values('bbb', '2017-08-16 17:08:04 +00:00', null);  create table child(id varchar(20), data varchar(10), data2 varchar(10), modstamp datetimeoffset, deleteddate datetimeoffset); insert child values('1', 'aaa', null, '2017-08-16 15:08:04 +00:00', null); insert child values('1', null, 'bbb', '2017-08-16 16:08:04 +00:00', null); insert child values('1', null, null, '2017-08-16 17:08:04 +00:00', null); insert child values('2', 'aaa', null, '2017-08-16 15:08:04 +00:00', null); insert child values('3', null, null, '2017-08-16 15:08:04 +00:00', null); 

you're query looks good. wouldn't call following query optimized, alternative way use row_number().

select * ( select *, rownum = row_number() on (partition id order modstamp) child (data = ? or data null or data2 = ? or data2 null) , modstamp <= '2017-08-16 17:08:04 +00:00' ) rownum = 1