I'll admit I may be ignoring some of the details, but I think the essence of the question is how to avoid a cartesian product when left joining to multiple tables. From your description, it seems that you want your result to really be more like a union of results from several joins.

Here's an example that I think shows a pattern you could follow, however you would have to be CAREFUL TO CHECK PERFORMANCE characteristics of the join to the Combi union.

with

Trade(TradeId) as (

select 1

union

select 2

union

select 3

),

TC(TradeId, TradeComment) as (

select 1 , 'TC 1a'

union

select 1 , 'TC 1b'

union

select 1 , 'TC 1c'

union

select 2 , 'TC 2a'

),

TA(TradeId, TradeAuditAction) as (

select 1 , 'TA 1a'

union

select 1 , 'TA 1b'

),

Combi(TradeId, TradeComment, TradeAuditAction) as(

select TradeId, TradeComment , null

from TC

union all

select TradeId, null , TradeAuditAction

from TA

)

select Trade.TradeId, Combi.TradeComment, Combi.TradeAuditAction

from Trade

left join Combi on Combi.TradeId = Trade.TradeId

It's not clear whether you really want a row in the result set for a Trade that has no matching rows in the three tables, but my example above assumes that you do. The answer would actually be simpler if you do not want such a row.. something like ....

-- with Trade, TC, TA as above

select Trade.TradeId, TC.TradeComment, null as TradeAuditAction

from Trade

join TC on TC.TradeId = Trade.TradeId

union all

select Trade.TradeId, null, TA.TradeAuditAction

from Trade

join TA on TA.TradeId = Trade.TradeId