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 2 of 2
  1. #1
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post

    sql returning too many results on JOIN

    hi guys,

    I have three tables which I'm joining...

    AGENTS
    ========
    agent_id
    commission
    promo_code

    AGENT_PAYMENTS
    ==============
    payment_id
    agent_id
    amount

    SALES
    ========
    sale_id
    promo_code
    status

    I need to select all the sales info where an agent's promo code has been used (and the status of the sale is "C" (complete)). THe trouble is, the SQL I've used is doubling, trebling etc the sum of the payments made to an agent - this depends on how many results are returned for the number of sales... eg: 4 sales means the SUM is being multiplied by 4.

    here's the SQL

    Code:
    SELECT 
    agents.agent_commission as commission,
    sum(sales.sale_total) as sales_total,
    sum(agent_payments.amount) as amount_paid
    FROM sales
    JOIN agents ON sales.promo_code = agents.promo_code
    JOIN agent_payments ON agent_payments.agent_id = agents.agent_id
    AND sales.status='C'
    AND agents.agent_id = '1'
    GROUP BY agent_payments.agent_id;
    where is it going wrong??

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Your query needs to put the conditions of the query in a WHERE clause, not as part of the JOIN clause. At least I think this is the problem... I usually have to run a query to find out before I'm confident about my logic. But anyway give it a try...

    Code:
    SELECT 
    agents.agent_commission as commission,
    sum(sales.sale_total) as sales_total,
    sum(agent_payments.amount) as amount_paid
    FROM sales
    JOIN agents ON sales.promo_code = agents.promo_code
    JOIN agent_payments ON agent_payments.agent_id = agents.agent_id
    
    WHERE sales.status='C'
    AND agents.agent_id = '1'
    
    GROUP BY agent_payments.agent_id;


  •  

    Posting Permissions

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