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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need outer set data based on filtered inner set
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 02/23/2013 :  15:12:52  Show Profile  Reply with Quote
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.

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 02/24/2013 :  23:28:31  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000