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 |
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 tablesAccountEntryDetail TableCode RefNo Debit Credit1111 00001 6002222 00001 600PostDatedChecks TableRefNo CheckNumber00001 1234500001 1234500001 12345[Code]Select a.Code,a.RefNo,b.CheckNumber,a.Debit,a.CreditFrom AccountEntryDetail aLeft Outer Join PostDatedChecks b on a.RefNo=b.RefNo------------Result from the above statementCode RefNo Check# Debit Credit1111 00001 12345 6001111 00001 12345 6001111 00001 12345 6002222 00001 12345 6002222 00001 12345 6002222 00001 12345 600Expected ResultCode RefNo Check# Debit Credit1111 00001 12345 6002222 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.CreditFrom AccountEntryDetail a Left Outer Join PostDatedChecks b on a.RefNo=b.RefNo |
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2004-08-25 : 01:37:48
|
Oh,Sorry for the PostDatedCheck Table. That should have beenPostDatedChecks TableRefNo CheckNumber00001 1234500001 6789000001 55699I tried your suggestion but still gives me the same result. |
|
|
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 |
|
|
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 aWHERE CheckNumber = 12345 to your SQL ?!/rockmoose |
|
|
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... |
|
|
|
|
|
|
|