sql record query

[swathys]

hi,

I have problem with my sql query. when i used direct insert statement in 2 different table and sort out using select statement by combining these 2 table's it perfecly give's the output that i wanted but when i apply the same method to the real application its working fine where it does 1 insert at a time to each table and when i sort it out using the select statement the output is ok as what i wanted but the select query goes wrong after the 3 and more records insert.....when i open the table it has only 1 record for each ID but when i combine and sort it out it show repetation of same ID more than once and it hapen the same even i use SELECT DISTINCT.

I have no idea what's the problem with this query...It works fine on 1 and 2nd record insertion but goes wrong when 3rd and more records.....Please help !
Below is my sql query....Realy hope for the solution....

use CreditLimit
Select distinct R.ID,R.KioskID, R.SubID,R.TransactionDate, (R.GrandTotal + D.PaymentToDealer) As 'GrandTotal',
D.PaymentToDealer, R.GrandTotal As 'Balance', R.Status
From RetailerBalance R, DealerTransaction D
Where R.KioskID = 'ABC123'
and R.SubID = D.SubID
ORDER BY R.ID,R.TransactionDate ASC

[adam_k]

Your join doesn't have enough keys, so you are getting a kartesian product.
Either use more fields to make the record from table PaymentToDealer uniquely match the appropriate record from table DealerTransaction or create a common key with a unique value for every record in both tables.

PS: Why are you only using SubID and not ID and SubID in your join?

[swathys]

thank you. Will try on this