[Resolved] Help Untangling this SQL Query

cmdr_bond

I'm trying to make an SQL query across 3 tables and am having trouble doing it.
I have the following tables:

Associates:
AssociateID | AssociateName | Email | Phone

Clients:
ClientID | ClientName | ClientDescription | AssociateID

Sales:
TxnID | ClientID | ProductID | Quantity

(it's a simplized version of my database)

What the basic association is that associates manage clients and the clients list their sales on my site of the various products they sell. What I want to somehow query, given an AssociateID, and find out how many distinct() clients an associate has (Count()) when sum()ming up all the quantities of products sold for that client under the sales table (for example, setting the theshold at sum(quantity) = 100)

Can this be done using the JOIN command, or do I need to run a bunch of loops to run through each line of the Sales table in order to count each distinct client's sales?

Thanks a lot

laserlight

Observing your database schema, the relationship between associates to clients is one-many.
If you are given an AssociateID then you dont need to query the Associates table since that is all you need to query the Clients table to find out which clients are related to a given associate.

What I want to somehow query, given an AssociateID, and find out how many distinct() clients an associate has (Count()) when sum()ming up all the quantities of products sold for that client under the sales table (for example, setting the theshold at sum(quantity) = 100)

I dont understand what you are trying to do here. Could you explain and elaborate?

cmdr_bond

Hi I currently have the following SQL for the SQL query:

$result5 = mysql_query("SELECT COUNT(DISTINCT Sales.ClientID) as 'numclients' FROM Clients, Sales WHERE (Clients.AssociateID='$AssociateID' AND Clients.ClientID=Sales.ClientID AND (SUM(Sales.Quantity) > 100))",$db);

But it's giving me an error. Taking off the last piece that checks to see whether or not the associate's client has had over 100 quantity sales stops the error. What am I doing incorrectly?

Thanks

madmada

If I understand you correctly, you want to know how many distinct clients an associate has whose total quantity from all transactions in the sales table is greater than a threshold (in this example 100). If so, this query should work for you:

SELECT clients.clientID 
  FROM clients JOIN sales USING (clientID) 
 WHERE clients.associateID = <enter given associateID here>
 GROUP BY sales.clientID HAVING sum(sales.quantity) > 100;

This will give you a result set containing the clientID's of each of the associate's clients who have a total quantity above the threshold. To get a count, simply count the number of rows in the result set. If you're using MySQL, you could use mysql_num_rows()

As Laserlight said, as you are given an associateID, you don't need to reference the associate table. If you want to get a listing of the number of clientID's for each associateID in the associates table, that gets a little more tricky.

Cheers

cmdr_bond

Thanks, it works!