SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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:
    Code:
    op_reject   spv_reject
    1                1
    2                2
    1                2
    2                1
    And the sum of both is 6 and 6.


Bookmarks

Posting Permissions

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