• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query gives the wrong data

 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Paul Clapham
Sheriff
Posts: 21322
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Make two queries : an inner query joining both tables and getting all necessary columns. An outer query grouping the results of the inner query.
 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, that's the reason. Some rows in your query appear multiple times in the result because of the join. Christophe's advice will avoid that.
 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Martin for your reply
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic