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
 Old Forums
 CLOSED - General SQL Server
 Left Outer Join (question)

Author  Topic 

rpc86
Posting Yak Master

200 Posts

Posted - 2004-08-25 : 01:13:26
Hi guys,

Thanks for your recent answers to my problems. Now, my problem is all about LEFT OUTER JOIN that returns me 6 records instead of 2. Here is my tables

AccountEntryDetail Table

Code RefNo Debit Credit
1111 00001 600
2222 00001 600


PostDatedChecks Table

RefNo CheckNumber
00001 12345
00001 12345
00001 12345

[Code]
Select
a.Code,
a.RefNo,
b.CheckNumber,
a.Debit,
a.Credit
From AccountEntryDetail a
Left Outer Join PostDatedChecks b on a.RefNo=b.RefNo

------------
Result from the above statement

Code RefNo Check# Debit Credit
1111 00001 12345 600
1111 00001 12345 600
1111 00001 12345 600
2222 00001 12345 600
2222 00001 12345 600
2222 00001 12345 600


Expected Result
Code RefNo Check# Debit Credit
1111 00001 12345 600
2222 00001 12345 600
[/Code]


Again, many thanks to you all.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-25 : 01:32:07
Why have you got dupe's in there anyway???

To fix it, all you need to do is add the DISTINCT keyword:
Select DISTINCT a.Code, a.RefNo, b.CheckNumber, a.Debit, a.Credit
From AccountEntryDetail a Left Outer Join PostDatedChecks b on a.RefNo=b.RefNo


Go to Top of Page

rpc86
Posting Yak Master

200 Posts

Posted - 2004-08-25 : 01:37:48
Oh,

Sorry for the PostDatedCheck Table. That should have been


PostDatedChecks Table

RefNo CheckNumber
00001 12345
00001 67890
00001 55699

I tried your suggestion but still gives me the same result.
Go to Top of Page

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-25 : 02:24:43
Not suprised.
If you do a standard join on just the RefNo, you will always get 6 records returned. Can you explain a bit more about what you're trying to do?
It is possible to write a query to do this, but I suspect it won't be what you're after
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-25 : 03:59:18
It is the RefNo that gives the "duplicates".
It is not about LEFT JOIN, a INNER JOIN would give the same result.
RefNo - 00001 has 3 occurences in the PostDatedCheck table, and 2 occurences in the AccountEntryDetail.
For each RefNo 00001 in AccountEntryDetail you will get 3 matches in the PostDatedCheck table.
2*3 = 6.

Just maybe it is as easy as adding a
WHERE CheckNumber = 12345 to your SQL ?!

/rockmoose
Go to Top of Page

achamarthy
Starting Member

26 Posts

Posted - 2004-08-25 : 14:45:21
I think you need to have one more clause for where condition to get your desired dataset like check# = XXX as roclmoose pointed...
Go to Top of Page
   

- Advertisement -