We need to copy some columns for new and amended clients from an in-house database to a 3rd-party one. The original process involved copying the data to an intermediate database...

I would try;

select distinct top 100

i.invoice_id, i.buyer_code, i.submit_time, i.request_id

from

dbo.invoice i

inner join dbo.invoice_adj ia on i.invoice_id = ia.invoice_id and ia.amount !=0

left outer join dbo.invoice_detail iad on i.invoice_id = iad.invoice_id

left...