title: LEFT OUTER JOIN with a WHERE clause
body: <p>I have two tables. <code>indRailType</code> contains a list of the names paired with an ID value that I use in other tables to indicate the rail type. <code>WO_BreakerRail</code> contains a date column and a rail code column that corresponds to the same code in <code>indRailType</code> and some other data. There's a row in <code>WO_BreakerRail</code> for any activity on each rail type, for every date. So I could have 3 rows dated for <code>3/19/2010</code>, each row indicates a different rail code, and what happened.</p>
  
  <p>When I use the following <code>LEFT OUTER JOIN</code>, I get a table with all the types of rail, with nulls in the rows where nothing happened on the 19th. Now, this is only working because I only have one date represented in my <code>WO_BreakerRail</code> table right now, the 19th. When I add more rows with different dates, things will go haywire.</p>
  
  <p>This is my SQL statement, which right now gives me exactly the results I want:</p>
  
  <pre><code>SELECT WO_BreakerRail.ID, indRailType.RailType, WO_BreakerRail.CreatedPieces, 
      WO_BreakerRail.OutsideSource, WO_BreakerRail.Charged, 
      WO_BreakerRail.Rejected, WO_BreakerRail.RejectedToCrop
  FROM indRailType
  LEFT OUTER JOIN WO_BreakerRail 
      ON indRailType.RailCode = WO_BreakerRail.RailCode
  </code></pre>
  
  <p>Now, when I add in a <code>WHERE WO_BreakerRail.Date = @Date</code> clause I lose all the rows in the <code>JOIN</code> which nothing happened. I don't want that. From reading up, it sounds like a FULL <code>OUTER JOIN</code> is what I want, but SQL Server Compact Edition doesn't support <code>FULL OUTER JOIN</code>s. Is there a way around this, or am I looking for something else entirely?</p>