Count on Multiple Tables
| From: | Ralph | Date: | Thu, 05 Jun 2003 04:35:26 +0000 |
| Subject: | Count on Multiple Tables | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-150296@lists.php.net to get a copy of this message | ||
I've been stuck on this one all morning. Can't seem to figure it out.
I have 2 tables, one with affiliate sales and another with affiliate
clickthroughs. I have to query both tables, so that I can get
clickthrough dates, hits, and then query affiliate sales table to get
number of orders for each date. I want to display the results like this:
DATE | TOTAL HITS | TOTAL SALES
05/03/2003 6 1
05/04/2003 7 0
I've managed to get dates and total hits by using the following query:
SELECT DATE_FORMAT(affiliate_clientdate, '%M %D, %Y') AS date, COUNT(*)
AS hits FROM affiliate_clickthroughs WHERE affiliate_id = '111' AND
affiliate_stores_id = '123' AND MONTH(affiliate_clientdate) = '06' AND
YEAR(affiliate_clientdate) = '2003' GROUP BY date";
But then I can't get the total number of sales on affiliate sales table.
Any suggestions? Your help is greatly appreciated.