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;
this code has an output:
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.
In your query you didn't specify any join condition, so all rows from the two tables that meet the select criteria will be cross-joined to each other. In this specific case, looking at the data in your database, you have two rows in each table that are selected, with 1 and 2 as reject values. The final join result is:
And the sum of both is 6 and 6.
This topic is now closed. New replies are no longer allowed.