List:General Discussion« Previous MessageNext Message »
From:René Fournier Date:January 30 2006 11:59pm
Subject:Re: How to restrict this query... (need subquery?)
View as plain text  
Hi Peter,

Yes, after I posted the question, I did some more reading and learned  
that it's possible to have several conditions in the ON clause. I tried:

SELECT
accounts.id,
accounts.account_name,
accounts.company_name,
history.msg_src,
COUNT(history.msg_src) as msg_num
FROM accounts
LEFT JOIN history ON history.account_id = accounts.id AND  
history.time_sec BETWEEN 1138604400 AND 1138652381
GROUP BY accounts.id, history.msg_src
ORDER BY accounts.id DESC, history.msg_src ASC

...and it does exactly what I need. Thanks for the reply though.

...Rene


On 30-Jan-06, at 4:12 PM, Peter Brawley wrote:

> René
>
> >What I need to do, somehow, is apply that WHERE clause
> >to the COUNT part of the SELECT. Any ideas?
>
> Did you try moving your WHERE condition to the ON clause?
>
> PB
>
> -----
>
> René Fournier wrote:
>> Hello,
>>
>> I have two tables: Accounts and History. Basically, I want to see  
>> how much activity each account has during a given period of time.  
>> Even if an account has no activity, I still want to see it in the  
>> result (naturally with zeros or null). In the history table, there  
>> is a column called time_sec—it's a UNIX timestamp. That is the  
>> column needed to restrict the counting to a particular day or  
>> month. My problem is that either I get all the accounts (good)  
>> without restricting to a day or month (bad)...
>>
>> SELECT
>>     accounts.id,
>>     accounts.account_name,
>>     accounts.company_name,
>>     history.msg_src,
>>     COUNT(history.msg_src) as msg_num
>> FROM accounts
>>     LEFT JOIN history ON history.account_id = accounts.id
>> GROUP BY accounts.id, msg_src
>> ORDER BY accounts.id DESC, history.msg_src ASC
>>
>> ... or I get a result that is restricted (good), but without  
>> showing all the accounts (bad)...
>>
>> SELECT
>>     accounts.id,
>>     accounts.account_name,
>>     accounts.company_name,
>>     history.msg_src,
>>     COUNT(history.msg_src) as msg_num
>> FROM accounts
>>     LEFT JOIN history ON history.account_id = accounts.id
>> WHERE
>>     history.time_sec > 1138604400 AND history.time_sec < 1138652381
>> GROUP BY accounts.id, msg_src
>> ORDER BY accounts.id DESC, history.msg_src ASC
>>
>> What I need to do, somehow, is apply that WHERE clause to the  
>> COUNT part of the SELECT. Any ideas?
>>
>> ...Rene
>> --MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql? 
>> unsub=peter.brawley@stripped
>>
>>
>>
>> --No virus found in this incoming message.
>> Checked by AVG Free Edition.
>> Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date:  
>> 1/27/2006
>>
>>
>
>
> -- 
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date:  
> 1/27/2006
>
>
>

Thread
How to restrict this query... (need subquery?)René Fournier30 Jan
  • Re: How to restrict this query... (need subquery?)Peter Brawley31 Jan
    • Re: How to restrict this query... (need subquery?)René Fournier31 Jan
  • Re: How to restrict this query... (need subquery?)Rhino31 Jan