|
thekarmikbob
Starting Member
1 Posts |
 Posted - 02/23/2013 : 15:12:52
|
I'm not coming up with the expected answers to these two problems (yes, public disclaimer, this is school work). I can build the inner query to come up with the right number of sales order numbers, but when I try to join it to the outer table, I get more rows than I should and I cannot figure out how to filter it back down.
Two tables, FactInternetSales which contains SalesOrderNumber, SalesOrderLineNumber, and OrderDate, and FactInternetSalesReason, which contains SalesOrderNumber(Key), SalesOrderLineNumber(key), and SalesReasonKey.
The question is to list all internet sales that do not have at least one reason associated with it, listing the SalesORderNumber, SalesOrderLineNumber, and OrderDate.
I built the inner query:
select distinct factinternetsales.salesordernumber
from factinternetsales
left join factinternetsalesreason
on factinternetsales.salesordernumber = factinternetsalesreason.salesordernumber
where factinternetsalesreason.SalesReasonKey is null
And this returns the 4.6k unique SalesOrderNumbers. The problem is then I have to pull in SalesOrderLineNumber and OrderDate from the factinternetsales table, which then re-expands the row count to 6.4k
salesorderdata sample
salesordernumber salesorderlinenumber orderdate
SO70740 1 2008-05-16 00:00:00.000
SO70740 2 2008-05-16 00:00:00.000
This is one of the sets I think is causing the issue... SalesOrderNumber SO70740 has no values in the InternetSalesOrderReason table, therefore matches the pattern of sales without a reason. But since there are two entries of SO70740, I get both... since they have unique salesorderlinenumbers, I cannot use distinct.
Suggestions appreciated, this assignment is already late. Thanks in advance. |
|
Need outer set data based on filtered inner set
