急求SQLserver sql语句
有如下4张表e_building_src、e_floor、e_floor_flatplan、e_flatplan。
现在做关联查询：
e_building_src 有字段ID、nature_number、land_id.ID为主键
e_floor 有字段floor_id、nature_number、land_id,floor_id为主键，
e_floor_flatplan 有字段ID、floor_id、flatplan_id,ID为主键,floor_id为e_floor表的外键，flatplan_id为e_flatplan表ID的外键
e_flatplan 有字段ID、dwgfile、dwgfilename，ID为主键。
举例
e_building_src e_floor e_floor_flatplan
ID nature_number land_id flood_id nature_number land_id ID floor_id flatplan_id
1 1 1 1 1 1 1 1 01
2 2 1 2 1 1 2 1 02
3 3 1 3 2 1 3 2 03 
4 1 2 4 3 1 4 3 04
5 2 2 5 1 2 5 3 05  
6 1 3 6 2 2 6 4 06
  7 1 3 7 5 07
  8 6 08
  9 6 09
  10 6 10
  11 7 11 
现在要求是我要先上面三张表关联得到在e_floor_flatplan表同一个floor_id下取最大的flatplan_id值的记录。再通过flatplan_id与第四张表e_flatplan 的ID关联 查询第四张表相关的记录



------解决方案--------------------
有前两张表什么事？简单问题复杂化：

SQL code

select * from e_floor_flatplan a, e_flatplan b
where a.flatplan_id=b.id
and not exists (select 1 from e_floor_flatplan where floor_id=a.floor_id and flatplan_id>a.flatplan_id)