Here's one without a temp table, but it does use multiple outer theta (inequality) joins...I'm sure SQL server will find some way of making the 4 joins to [child] a bit more efficient - at least it will probably sort the data from [main] and use merge joins rather than loops.

THe query brings back the first three child records ordering by [id] (primary key). If you have a wide primary key (e.g. varchar), the joins might be a bit sluggish.

[id] is primary key on both tables. [main_id] is the foreign key to [main] from [child].

I haven't included any other fields for simplicity, but you can would just add them to the 'select' list.

select m.id parentPK,

c1.id childPK1,

c2.id childPK2,

c3.id childPK3

from main m

left join child c1

on c1.main_id = m.id

left join child c2

on c2.main_id = m.id

and c1.id < c2.id

left join child c3

on c3.main_id = m.id

and c2.id < c3.id

left join child c_dummy

on

c_dummy.main_id = m.id

and

(

c_dummy.id < c1.id

or

(

c_dummy.id > c1.id

and c_dummy.id < c2.id

)

or

(

c_dummy.id > c2.id

and c_dummy.id < c3.id

)

)

where c_dummy.id is null