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
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant