How to write a SQL query based on using following input to calculate below output?

Question

Input:

Output:

I am trying to write this query in SQL server.

Answer 1

Try this:

select isnull(isnull(t1.id,t2.id),t3.id),t1.tid,t2.oid,t3.pid
from test_tid t1 full outer join test_oid t2
on (t1.id = t2.id)
full outer join test_pid t3
on (t1.id = t3.id)

Answer 2

Just use LEFT JOIN and do select the fields like shown below,

select t.id, tt.tid, ot.oid, pt.pid
from test t
left join test_tid tt on t.id = tt.id
left join test_pid pt on t.id = pt.id
left join test_oid ot on t.id = ot.id