Try this code and see if it works for you:

select m.id, m.letter, m.data1, coalesce(c1.letter, c2.letter), coalesce(c1.data1, c2.data1)

from @Mother m

left join @child1 c1 on c1.letter = m.Letter

left join @child2 c2 on c2.letter = m.Letter

NOTE: You may have to change some of the table names to match your tables.

Also, for future posts, it would be nice if you could post your DDL and Insert statements like this:

declare @Mother table

(

ID int,

Letter char(1),

data1 int

)

insert @Mother (id, letter, data1) values

(1, 'A', 23)

,(2, 'B', 24)

,(3, 'C', 25)

,(4, 'D', 12)

,(5, 'E', 14)

,(6, 'F', 10)

,(7, 'G', 12)

declare @child1 table

(

ID int,

Letter char(1),

data1 int

)

insert @child1 (ID, Letter, data1) values

(1, 'A', 123)

,(2, 'B', 124)

,(3, 'C', 125)

declare @child2 table

(

ID int,

Letter char(1),

data1 int

)

insert @child2 (ID, Letter, data1) values

(1, 'D', 127)

,(2, 'E', 128)

,(3, 'F', 129)

,(4, 'G', 130)

It makes it so much easier and faster to come up with a possible solution.