[Solved]Complex Query Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

[Solved]Complex Query Help

Post 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?
Last edited by AGISB on Mon Oct 04, 2004 1:06 pm, edited 1 time in total.
bling
Forum Commoner
Posts: 25
Joined: Mon Jul 12, 2004 12:44 pm

Post 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
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

joins are a blast once you understand how they work ;)
Post Reply