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.
| Author |
Topic |
|
shaunos
Starting Member
8 Posts |
 Posted - 2010-10-08 : 04:20:18
|
| Hi,I'm no sql guru so I'm pulling my hair out on this one.I have a view which links 3 tables to generate some customer info.It returns just over 3500 results.When I left join this view with a billing table I only get 2500 results!Here is the code for the join in question:<SNIP>SELECT vw_ChurnedCustomers.*, tbl_Transactions.Amount AS [2ndLastBill]FROM vw_ChurnedCustomers LEFT OUTER JOIN tbl_Transactions ON vw_ChurnedCustomers.CustomerID = tbl_Transactions.CustomerID WHERE (tbl_Transactions.TransactionType = 1)AND dbo.fn_LastDayOfMonth(DATEADD(month, - 1, vw_ChurnedCustomers.CancelDate)) = tbl_Transactions.EntryDate</SNIP>Thanks in advance...~S. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-08 : 04:41:17
|
tbl_Transactions.TransactionType = 1is filtering the resultset.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
shaunos
Starting Member
8 Posts |
Posted - 2010-10-08 : 04:57:12
|
| Well duh!!Don't I feel stooopid :) At least I didn't claim to be a guru!Isn't it amazing how obvious something is when it's pointed out to you?Thanks mate, I think I was suffering from a case of 'forest for the trees' syndrome with this one...~S. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-08 : 05:30:31
|
 It happens to all of us sometimesYou are welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
Left join returns fewer results than left table!