title: get all rows from one table, sum on column from a second table using a where clause
body: <p>I want to get all rows from table1 and I want to get a total of a column in table3 for each person in table1. I am able to do this if I don't use a where clause anywhere in my query (in the sub select, inner join or regular where clause). If I use a where clause like below, only the people in table1 that have records in table2 between a supplied date and have been verified.</p>
  
  <p>What I want is all rows from table1 but sum only the records in table2 that are between the supplied date and have been verified.</p>
  
  <p>Is there any way to do this in a single query?</p>
  
  <pre><code>SELECT table1.ID
       , table1.LName
       , table1.FName
       , (Select SUM(table3.DollarValue) Where table2.Verified = 1) AS total
  FROM table3 
  INNER JOIN table2       ON table3.IncentiveID = table2.IncentiveID 
  RIGHT OUTER JOIN table1 ON table2.ID = table1.ID
  Where table2.date &gt;= '1/1/2012'
    AND table2.date &lt;= '12/31/2012'
    AND table2.Verified = 1
  Group By table1.ID, table1.LName, table1.FName, table2.Verified
  Order By table1.LName, table1.FName
  </code></pre>
  
  <p>Thank you in advance for any help!</p>