Need outer set data based on filtered inner set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need outer set data based on filtered inner set

Author

Topic

thekarmikbob
Starting Member

1 Post

Posted - 2013-02-23 : 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-24 : 23:28:31

why do you need to show lines in this case? as you say you just want to list distinct of orders so order number is sufficient, take out linenumber field from select and you'll get expected output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources