Page 1 of 1

[Solved]Complex Query Help

Posted: Mon Oct 04, 2004 6:30 am
by AGISB
Here is what I got:

I got a table with webmasters and one with customer billing data.

I now want to query a list of each webmasters earnings that were referred by another webmaster.

So basically it should sum the Amounts for each webmaster out of the monthly billing table.

I think 2 queries might do the job:

1 to determine the webmasters

Code: Select all

SELECT Web_ID FROM Webmasters WHERE Referrer = '10152'
and then loop thru all webmasters with the 2.

Code: Select all

SELECT SUM(m.Amount) FROM Customers c, Monthly_Billing m WHERE c.Webmaster = '$webmaster' AND m.Customer = c.Customer
Can I put this in one query?

Posted: Mon Oct 04, 2004 9:12 am
by bling
Try this...

Code: Select all

SELECT
 SUM(m.Amount),
 c.Webmaster
FROM
 Customers c,
 Monthly_Billing m
WHERE
 m.Customer = c.Customer 
GROUP BY
 c.Webmaster

Posted: Mon Oct 04, 2004 11:00 am
by AGISB
Thanks bling


this works for all webmasters who have active customers in monthly_billing

This would still require me to do a 2. query as I want only the webmaster numbers of webmasters that were referred by another webmaster with the id '10152' and getting all would require me to query this in a 2nd query

Posted: Mon Oct 04, 2004 11:05 am
by feyd

Code: Select all

SELECT
SUM(m.Amount),
c.Webmaster
FROM
Customers c,
Monthly_Billing m,
Webmasters w
WHERE
m.Customer = c.Customer
AND w.id = c.Webmaster
AND w.Referrer = '10152'
GROUP BY
c.Webmaster
then, or similar.

Posted: Mon Oct 04, 2004 11:26 am
by AGISB
That works beautifully. Thanks feyd.


I definately have to look into joins more closely in particular the ones with 3 or more tables.

Posted: Mon Oct 04, 2004 11:27 am
by feyd
joins are a blast once you understand how they work ;)