select e.*,g.area from ( select c.groupname,b.*,a.id as handid from handdetail a ,notifyinfo b,[group] c where a.notifyid=b.id and c.id=b.groupid and b.actionid>=4 and (b.prostate is null or b.prostate =0) and stateid=1 ) e LEFT OUTER JOIN [area] g on g.id=e.areaid order by e.areaid,e.cph 