title: Rewriting a sp without subquery
body: <p>Is there a way to rewrite this SP, without a subquery. Basically the I.RemarkId can sometimes be null, i was trying to use a left join or left outer join. I want to return rows from flightinfo always and return the remark when the I.RemarkId is not null and just null in the  remark column when I.remarkId is null. </p>
  
  <pre><code>ALTER PROCEDURE [dbo].[Peach_GetFlightInfoForRoute]
      @FlightDate datetime,   
      @Origin nvarchar(3),
      @dest nvarchar(3),
      @Lang nvarchar(2)
  AS
  SELECT 
       I.FlightNumber
      ,FlightDate
      ,STD
      ,(select [Message] FROM FlightRemarkDetail WHERE RemarkId = I.RemarkId WHERE LangCode = @Lang) As [Remark]
  FROM 
       [FlightInfo] as [I]
  JOIN
       [FlightNumbers] as [N]
  ON  
      I.FlightNumber = N.FlightNumber 
  WHERE 
      FlightDate = @FlightDate AND (@Origin='' OR @Origin = N.Origin) AND (@dest = '' OR @dest = N.Destination)
  
  FlightInfo
  ===========
  FlightNumber    nvarchar(16)
  FlightDate  datetime    
  STD nvarchar(4) 
  RemarkId int NULL
  
  FlightRemarkDetail
  ==================
  RemarkDetailId  int 
  RemarkId    int NOT NULL
  LangCode    nvarchar(2) 
  Message nvarchar(512)   
  </code></pre>