By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

Help with query

P: 24
Hello

I am trying to compile a query but getting inconsistent data from it. I need to make use of two tables namely:

transaction_products [contains products related to a transaction in transactions table]
refunds [contains refunded product details related to a transaction in transactions table]

Both of the above tables contain product_id and I would like to join the tables to gather some detailed information about sales. I know it sounds like a simple job but I keep getting inconsistent results from the join, here is the sample query I am using to join:

SELECT transaction_products.id, sum(transaction_products.qty), sum(refunds.price) FROM refunds NATURAL JOIN transaction_products GROUP BY transaction_products.id;

Now when I run a group by query on a single table I get the sum calculated fine, however after doing a join like the above I get the amounts to be higher than their original sum in all the products for both the qty and the price. I am not sure where I am going wrong with this, could someone pleas e shed some light on this problem and put me in the right direction.

Thanks
May 9 '09 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 700
The query looks ok. It would be hard to diagnose the problem without the table data. But I guess tables are too big :) to put it here.
May 10 '09 #2

P: 24
Hello

Thanks for replying, apparently it was due to bad data in the refunds table which was messing up the results for the JOIN. After trying "NATURAL FULL JOIN" things seemed to start working and the results are looking fine aswell. I think it was rather a human error :) in trying to understand the formation of the JOIN.

Thanks
May 10 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.