这样的主从表查询语句如何编写？

现有两个主从明细表，表结构如下：
table1：
MasterID         Name
1       aaa
2       bbb
3       ccc
-------------------------------------------
table2：
ID MasterID                   State CreatedDate
1 2 开始 2007-6-6
2 2 执行中 2007-6-8
3 2 完成 2007-6-10
4 3 开始 2007-6-7
5 3 执行中 2007-6-9
--------------------------------------------
现在想得到如下的查询结果：
MasterID                   Name Laststate     LastDate
1 aaa 未开始
2 bbb 完成 2007-6-10
3 ccc 执行中 2007-6-9
---------------------------------------------
也就是说如果从表（table2）中，没有从记录,则Laststate字段就显示“未开始”，LastDate留空。如果有从记录的，就只把最后时间的记录与主表（table1）合并。
这样的查询语句如何编写？谢谢！

------解决方案--------------------
select
a.MasterID,
a.Name,
isnull(b.State, '未开始 ') as LastState,
b.CreatedDate as LastDate
from
table1 a
left join
table2 b
on
a.MasterID=b.MasterID
and
not exists(select 1 from table2 where MasterID=b.MasterID and CreatedDate> b.CreatedDate)
------解决方案--------------------
Select a.MasterID,a.Name,IsNULL(b.State, '为开始 '),b.CreatedDate as LastDate
from table1 as a Left Join table2 as b on a.MasterID=b.MasterID
and not exists(Select * from table2
where MasterID=b.MasterID and CreatedDate> b.CreatedDate)
------解决方案--------------------
Select
A.*,
(Case When D.MasterID Is Null Then N '未开始 ' Else D.State End) As Laststate,
(Case When D.MasterID Is Null Then Null Else D.CreatedDate End) As LastDate
From
table1 A
Left Join
(Select B.* From table2 B Inner Join (Select MasterID, Max(CreatedDate) As CreatedDate From table2 Group By MasterID) C On B.MasterID = C.MasterID And B.CreatedDate = C.CreatedDate) D
On A.MasterID = D.MasterID

------解决方案--------------------
--方法一
Select
A.*,
(Case When D.MasterID Is Null Then N '未开始 ' Else D.State End) As Laststate,
(Case When D.MasterID Is Null Then Null Else D.CreatedDate End) As LastDate
From
table1 A
Left Join
(Select B.* From table2 B Inner Join (Select MasterID, Max(CreatedDate) As CreatedDate From table2 Group By MasterID) C On B.MasterID = C.MasterID And B.CreatedDate = C.CreatedDate) D
On A.MasterID = D.MasterID

--方法二
Select
A.*,
(Case When D.MasterID Is Null Then N '未开始 ' Else D.State End) As Laststate,
(Case When D.MasterID Is Null Then Null Else D.CreatedDate End) As LastDate
From
table1 A
Left Join
(Select B.* From table2 B Where Not Exists(Select MasterID From table2 Where MasterID = B.MasterID And CreatedDate > B.CreatedDate)) D
On A.MasterID = D.MasterID


--方法三
Select
A.*,
(Case When D.MasterID Is Null Then N '未开始 ' Else D.State End) As Laststate,
(Case When D.MasterID Is Null Then Null Else D.CreatedDate End) As LastDate
From
table1 A
Left Join
(Select B.* From table2 B Where CreatedDate = (Select Max(CreatedDate) From table2 Where MasterID = B.MasterID)) D
On A.MasterID = D.MasterID
------解决方案--------------------
select table1.MasterID ,Name,(case when table2.State is null then N '未开始 ' else table2.State end ) as Laststate ,