Difficult Join

Hi all,

I need help building a query please. I have 3 tables:

OPPORTUNITIES (opportunity_id, opportunity_name, business_name, amount, status, estimated_close...)

PAYMENTS (opportunity_id, business_name, amount, payment_type, date...)

CUSTOMERS (business_name, contact_name, prospect_source, telephone, address...)

An opportunity is a sales term for a possible sale of services to a client. Each payment belongs to an opportunity (sometimes customers make payments in small quantities, so we can have multiple payments belonging to the same opportunity). prospect_source is the name of the individual who referred that customer for business. Kind of like a referrer.

What I would like is to get a list of top referrers - people who have introduced the most business to the company.

I already have a way of getting the top referrer by number of companies introduced:


SELECT prospect_source,
COUNT( DISTINCT(business_name) ) AS customers_per_prospect
FROM customers
GROUP BY prospect_source
ORDER BY customers_per_prospect DESC;


And I already have a way of getting the sum of all payments made against an opportunity:


SELECT opportunities.business_name,
SUM(payments.amount) AS revenue_per_opportunity
FROM opportunities
INNER JOIN payments
ON opportunities.opportunity_id=payments.opportunity_id
GROUP BY opportunities.business_name
ORDER BY revenue_per_opportunity DESC;


What I would like is a way of getting the top referrer by amount of revenue brought in.

So we need to somehow use information from all 3 tables.

Does anyone have any idea of how to do this?

*EDIT* I forgot to mention that I'd like to present these results in a table called "Top Referrers", with the ability for the user to sort the results either by "Number Of Customers" or by "Revenue Brought". My point is that I need a query that will somehow incorporate the first snippet of code I posted above.

Thanks in advance
Sean

I figured it out:


SELECT c.prospect_source,
COUNT( DISTINCT(c.business_name) ) AS customers_per_prospect,
SUM(p.amount) AS revenue_per_prospect
FROM opportunities o
LEFT OUTER JOIN customers c ON o.business_name = c.business_name
RIGHT OUTER JOIN payments p ON o.opportunity_id = p.opportunity_id
GROUP BY prospect_source
ORDER BY revenue_per_prospect DESC