Matt:
The left joint query take 4 ever to generate - in fact - I left it 5 mins and still no results.... I have done it for the moment with a loop through php and querying the vtconlineusers table by creation date/dealercode for each return hits date, but that takes too long also - 30 seconds for year to date on some affiliates.... Any other suggestions out there? P.S. The affiliate_stats database has about 3,000 records, while the vtconlineusers database has about 9,500 records On 9/2/03 8:01 PM, "Matt W" <[EMAIL PROTECTED]> wrote: > Hi Mike, > > If you use a LEFT JOIN, I think you'll get the results you want. > Something like > > SELECT afs.stat_date, afs.hits, COUNT(v.clientcode) AS signups > FROM affiliate_stats AS afs > LEFT JOIN vtconlineusers AS v ON (v.creation_date=afs.stat_date AND > v.dealercode=afs.affiliate_id) > WHERE affiliate_id='a280' AND afs.stat_date BETWEEN '2003-01-01' AND > '2003-09-31' > GROUP BY afs.stat_date > > Hope that helps. > > Matt > > > ----- Original Message ----- > From: "Mike Morton" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, September 02, 2003 6:27 PM > Subject: Query not returning 0 count records.... > > >> In the following query: >> >> select afs.stat_date,afs.hits,count(v.clientcode) as signups from >> affiliate_stats as afs,vtconlineusers as v where affiliate_id='a280' > and >> afs.stat_date between '2003-01-01' and '2003-09-31' and > v.creation_date = >> afs.stat_date and v.dealercode=afs.affiliate_id group by afs.stat_date > order >> by afs.stat_date >> >> I get a result set that returns only hits where there is a value for > the >> count(v.clientcode) - if there is not a value for the > count(v.clientcode) >> for that particular date, then there is not a 0 returned... Okay - a > bit >> confusing... Here is a better attempt to explain: >> >> I am trying to do a query to list hits and signups for a service. The > hits >> are recorded in a table called affiliate_stats and has a structure of: >> Affiliate id (varchar) >> Stat_date (date) >> Hits (int) >> >> The signups are recorded in a table called vtconlineusers and has a >> structure of: >> Clientcode (primary key auto_increment) >> Creation_date (date) >> >> And various other non-important fields. >> >> I want to get a count by date for a range of dates of signups and >> clickthroughs. The problem is that if there are no signups for a > particular >> date, the number of clickthrougs is not returned in the record set > either, >> so I end up with something like: >> 2003-01-01:10:1 >> 2003-01-06:32:2 >> And so on when it *should* (or at least I want it to) return: >> 2003-01-01:10:1 >> 2003-01-02:6:0 >> 2003-01-03:15:0 >> 2003-01-04:2:0 >> 2003-01-05:8:0 >> 2003-01-06:32:2 >> >> And so on.... >> >> So, hopefully that was a bit clearer.... >> >> Is it possible to return results like this, and if so, what is wrong > with my >> query? >> >> TIA! >> >> >> >> >> -- >> Cheers >> >> Mike Morton > -- Cheers Mike Morton **************************************************** * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * **************************************************** "Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple." - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
