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 |
|
barnabeck
Posting Yak Master
236 Posts |
 Posted - 2011-10-14 : 03:12:37
|
| Are there possible rare circumstances where a LEFT OUTER JOIN gives less results than it has records in table A? Dont matter what I do, like adding a condition that gives no match, the amount of records returned is always exactly ONE less than I do have in table A. WHYYYY??select * from LEDGERTRANS WHERE (LEFT(dbo.LEDGERTRANS.ACCOUNTNUM, 1) = '2') AND (LEN(dbo.LEDGERTRANS.ACCOUNTNUM) = '6') => 14250 rowsSELECT * FROM dbo.LEDGERTRANS LEFT OUTER JOIN dbo.LEDGERJOURNALTRANS ON dbo.LEDGERTRANS.JOURNALNUM = dbo.LEDGERJOURNALTRANS.JOURNALNUM AND dbo.LEDGERTRANS.VOUCHER = dbo.LEDGERJOURNALTRANS.VOUCHER AND dbo.LEDGERJOURNALTRANS.DATAAREAID = 'wik ' AND (SUBSTRING(dbo.LEDGERJOURNALTRANS.ACCOUNTNUM, 4, 1) = '-' OR SUBSTRING(dbo.LEDGERJOURNALTRANS.OFFSETACCOUNT, 4, 1) = '-')WHERE (LEFT(dbo.LEDGERTRANS.ACCOUNTNUM, 1) = '2') AND (LEN(dbo.LEDGERTRANS.ACCOUNTNUM) = '6')=> 14249 rows |
|
|
barnabeck
Posting Yak Master
236 Posts |
Posted - 2011-10-14 : 03:43:47
|
| Uffff... how stupid!!!!Ok, my fault. I had 2 queries in the same window, executing together. The above one giving a total with just one number as a result. The amount of rows displayed in the lower status line regards the total of ALL rows among the 2 queries... and that's where I got the missmatch of one record more. I was about to freak out... how stupid I was. |
 |
|
|
|
|
|
LEFT OUTER JOIN... one is missing?!