sub query and Join in sql

http://technet.microsoft.com/en-us/library/cc917631.aspx