Databases
StackOverflow2013
Postsdbo
POOne Query, Three Tables, Show all rows in left table?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOne Query, Three Tables, Show all rows in left table?
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload