ADO/ADO.NET - getting duplication record in table when using left outer join

Asked By mani on 18-May-12 09:14 AM
Earn up to 20 extra points for answering this tough question.
hi i have 5 tables and am using left outer join like this


Select distinct t1.invoiceno,t1.sareeno,t1.Amount,t1.status, t2.InvoiceNo,t2.ShopId,t2.ShopName,t2.ContactPreson,t2.MobileNo,t2.Address, t3.Invoiceno,t3.Cname,t3.ReturnBillNo ,t3.Amount, t4.ReturnBillNo,t4.Invoiceno,t4.SareeNo,t4.Amount, t5.InvoiceBillNo,t5.InvoiceNo,t5.ShopId,t5.ShopName,t5.Cname,t5.Cmobileno,t5.Address,t5.Amount From Details_suspend  t1 inner join Suspend_Sales t2 ON  t1.InvoiceNo = t2.InvoiceNo
LEFT outer  JOIN Suspend_Return t3 ON  t3.Invoiceno = t1.Invoiceno
inner join Suspend_ReturnDetails t4 ON  t4.Invoiceno = t3.Invoiceno
LEFT outer JOIN WholeSale t5 ON  t5.InvoiceNo = t4.InvoiceNo


and have common values as invoiceno in all table..
but when i having 2 or more record in first 4 table
and in 5 th table only one row
its getting dupplicate record for all rows which is in first 4 table..
so totaly ia m getting 4 noof record (3 duplication)in my 5 th table...how to solve this..

and not able to use  group by...any help..if i have single record in my 5th table i need to get only single record and remainig all rows to be null when i grouping 5 tables???any help??
Vikram Singh Saini replied to mani on 25-May-12 06:26 AM
Well! I tried with same fields name, and dummy records( 2 rows in first four table) and single record in 5th table.

And I was able to retrieve all matching records from all four table. And in 5th table it was showing NULL in 2nd row for no matching value.

So I think that the query is working superb. However if you find that I didn't understand it properly please let us know by some snapshots of data in tables. And what you are getting after execution of query?