I have 2 tables, TransportPayment and TuitionPayment, both of them has these 3 columns : payment, due,discount. I want to get sum of payments, max of due and sum of discount of both the tables in a single query.

This is the query i wrote :

But, this query gives me the wrong datas. What correction does this query needs?

That's impossible to say without knowing something about the problem. Right now, all we know is that you don't like the answer you get. But without knowing what the right answer is, what the wrong answer you're getting is, and what the differences are, there's nothing to be said.

Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622

posted

0

Thanks for your reply Paul

I wrote a separate query for Transport and Tuition Payment (I want the combination of 2)

Query for TuitionPayment

Gets me :
Enrollmentno =1004
fullname = Chris
Tuition fee = 20000
sum(tp.payment) = 5000
sum(tp.discount) = 5000

Query for TransportPayment

This Gets me
Enrollmentno =1004
fullname = Chris
Transport fee = 5000
sum(tp.payment) = 1000
sum(tp.discount) = 0

When i want both the table details as a single row. The query i posted gives this output (specific to enrollmentno 1004)
Enrollmentno =1004
fullname = Chris
Tuition fee = 20000
Tuition Payment = 7000 //wrong data
Tuition Discount = 10000 //wrong data
Transport fee = 5000
Transport payment = 3000 //wrong data
Transport discount = 0 //wrong data

One important thing i need to tell, TuitionPayment table contains 3 records with enrollmentNo 1004, where as Transportpayment table contains 2 records with enrollmentno 1004. I think that's the reason why it gets incorrect data