I don't think it's impossible. It might be nicer if you could use a
sub-select. But here's join.
SELECT DISTINCT affiliates.id, DISTINCT
sales.client_id,SUM(sales.client_id)
FROM affiliates,clients,sales
WHERE affiliates.id = clients.affiliate_id AND sales.client_id =
clients.id;
Would this do it? That should tell you how many sales each affiliate has
made to each client. Sum that up, and you will get what you probably
want in the end - a total referral, but at the least it should show you
how the join would go.
Benjamen R. Meyer
> -----Original Message-----
> From: Daren Cotter [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, July 11, 2002 5:03 PM
> To: [EMAIL PROTECTED]
> Subject: Impossible Query?
>
>
> I have three tables, affiliates, clients, and sales.
>
> The affiliates table stores all of the information about affiliates,
> clients about clients, sales about sales. In the clients
> table, there is
> a field for affiliate_id (affiliates refer clients), and in the sales
> table there is a field for client_id.
>
> I need a query that will show me a list of all affiliates and
> the number
> of sales each affiliate has generated. I know this will involve a left
> join, but I can't figure it out, since it involves that third table.
>
> Which actually brings up another question: would be be better to store
> the affiliate_id in the sales table? The reason I do it this way, is
> because if an affiliate refers a client, and the client is involved in
> numerous sales, the affiliate should be credited each time.
>
> TIA!
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
