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


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??

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...


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;