Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
09-30-2013, 07:53 AM #1
- Join Date
- Aug 2010
- Thanked 0 Times in 0 Posts
Need help in select statement from two tables
I have a problem in my select query statement to get the value of reject from op_reject table and spv_reject table in one row
here is my query:
SELECT o.compound_type, SUM(o.reject) AS op_reject, s.compound_type, SUM(s.reject) AS spv_reject FROM op_reject AS o ,spv_reject AS s WHERE o.compound_type = 'P28' AND s.compound_type = 'P28' AND o.process_id = '2' AND s.process_id = '2' AND o.reject_date = '2013-09-30' AND s.reject_date = '2013-09-30' GROUP BY o.compound_type, o.process_id;
but it should only be:
because the sum of rejects are only 3.00
I attached my database for your reference.
Any help is highly appreciated.
Thank you so much.
09-30-2013, 12:23 PM #2
- Join Date
- May 2006
- Thanked 1 Time in 1 Post
add one more condition o.compound_type = s.compound_type
09-30-2013, 06:55 PM #3
Harsh is wrong. While it would be better in some abstract way to do
from a practical standpoint the codeCode:WHERE o.compound_type = s.compound_type AND s.compound_type = 'P28'is doing exactly the same thing.Code:WHERE o.compound_type = 'P28' AND s.compound_type = 'P28'
To find the problem, please use this query and show us the results:
Code:SELECT o.compound_type AS otype, o.reject AS oreject, s.compound_type AS stype, s.reject AS sreject FROM op_reject AS o ,spv_reject AS s WHERE o.compound_type = 'P28' AND s.compound_type = 'P28' AND o.process_id = '2' AND s.process_id = '2' AND o.reject_date = '2013-09-30' AND s.reject_date = '2013-09-30' ;
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.