title: One Query, Three Tables, Show all rows in left table?
body: <p>I have three tables I need to pull data for, the left table needs to always display the rows that are called out in the "where". My problem is that since there are three tables I can't get this to work. I have tried joins like crazy, it works when only doing two tables, but as soon as I get the third in there it doesn't work, it then will only display rows that are not null across all three tables. I need to be able to display the specified IDs in the first table regardless if the other two tables are completely null for the specified IDs</p>
  
  <p>I have a database where I only have read access, so changing the structure on this won't work. Below is my SQL code that works as it is to get me the data I want with the exception that there is a ID that doesn't display since it has no data for the day specified.</p>
  
  <pre><code>select
      [User].Id,
      [User].Name,
      convert(float,round(sum(SalesOrderJournalDetail.Price*SalesOrderJournalDetail.Shipped),2)) as 'Sales Yesterday', 
      convert(float,round(sum(SalesOrderJournalDetail.ActualCost*SalesOrderJournalDetail.Shipped),2)) as 'Cost Yesterday',
      count(distinct(SalesOrderJournalDetail.SalesOrderId)) as 'Orders Yesterday', 
      count(SalesOrderJournalDetail.SalesOrderId) as 'Lines Yesterday',
      convert(float,UserTotal.SalesMTD,2) as 'Sales MTD',
      round(convert(float,UserTotal.CostMTD,2),2) as 'Cost MTD'
  from 
      [User], UserTotal, SalesOrderJournalDetail
  where
      [User].Id in (' 725',' 150',' 239',' 225',' 209',' 227',' 222',' 232',' 241',' 215',' 214',' 722',' 134',' 201',' 238',' 721','M104',' 244',' 245',' 104')
  and convert(varchar(10),SalesOrderJournalDetail.InvoiceDate,111) = '2012/04/19'
      and [User].Id=SalesOrderJournalDetail.SalesPersonUserId
      and SalesOrderJournalDetail.SalesPersonUserId=UserTotal.UserId
  group by [User].Id, [User].Name, SalesOrderJournalDetail.SalesPersonUserId, UserTotal.UserId, UserTotal.SalesMTD, UserTotal.CostMTD 
  order by [User].Name
  </code></pre>
  
  <p>To help explain the structure, the [User] table displays the user's ID number and Name, the SalesOrderJournalDetail table displays the current day's sales, and the UserTotal table displays the monthly sales data (via some calculations).</p>
  
  <p>I need to display the day's sales and monthly sales from the latter two tables, and need to pull the user's name and ID from the first.</p>
  
  <p>All three table's unique data are [User].Id, SalesJournalDetail.SalesPersonUserId, and UserTotal.UserId</p>
  
  <p>Any ideas how I can get this to show all the ID's shown in the where cluase regardless if there are no day or monthly sales for that user?</p>
  
  <p>Oh and this is using Microsoft SQL</p>