Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Need help in select statement from two tables

    Hi,
    Good day!
    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:
    Code:
    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:
    P28|6.00|P28|6.00
    but it should only be:
    P28|3.00|P28|3.00
    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.
    Attached Files Attached Files

  • #2
    Regular Coder
    Join Date
    May 2006
    Posts
    101
    Thanks
    0
    Thanked 1 Time in 1 Post
    add one more condition o.compound_type = s.compound_type

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Harsh is wrong. While it would be better in some abstract way to do
    Code:
    WHERE o.compound_type = s.compound_type 
      AND s.compound_type = 'P28'
    from a practical standpoint the code
    Code:
    WHERE o.compound_type = 'P28' AND s.compound_type = 'P28'
    is doing exactly the same thing.

    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •