chris smith wrote:
> On 10/21/06, Dwight Tovey <[EMAIL PROTECTED]> wrote:
>> Hello all
>> Maybe it's been a long week, but I'm trying to do something that should
>> be
>> simple and just not getting anywhere.
>>
>> I have two tables:
>> accounts
>> acctid: int unique
>> acctowner: char
>> ...
>>
>> docs
>> docid: int unique
>> acctid: int
>> doctitle: char
>> ...
>>
>> I want to list my accounts along with a count of the documents
>> associated
>> with each account. I tried a simple GROUP BY:
>> SELECT a.acctid, a.acctowner, COUNT(d.acctid)
>> FROM accounts AS a, documents AS d
>> WHERE a.acctid = d.acctid
>> GROUP BY a.acctid;
>
> Change it to a left join:
>
> ... FROM accounts a LEFT OUTER JOIN documents d ON a.acctid=d.acctid
> GROUP BY a.acctid;
>
Bingo. I new it had to be something simple. I didn't even think of an
outer join. It all works fine now. This should save me from having to
select the accounts, then loop through to count the documents for each
account. I didn't like that idea at all.
Thanks again.
/dwight
--
Dwight N. Tovey
[EMAIL PROTECTED]
http://www.dtovey.net/~dwight/
Please Do Not send me Microsoft Word attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
-----------
About the time we think we can make ends meet, somebody moves the ends.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
