You want to look at 'group by acctSrv.accountID' rather than a compound select.
On Mon, 23 Dec 2002, Adam Nowalsky wrote: > Date: Mon, 23 Dec 2002 09:04:32 -0500 > From: Adam Nowalsky <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: sql help > > hi, wonder if the sql gurus can help with this one. i have two tables > (simplified), tblAccounts and tblAccountsServices. tblAccounts has an ID > (PK) and an accountNumber, and tblAccountsServices has an ID (PK) and > accountID (FK to tblAccounts.ID). i want to run a query that gives me a row > for each record in tblAccountsServices that looks like - > > tblAccountsServices.ID, tblAccounts.accountNumber, COUNT of > tblAccountsServices.ID for this tblAccountsServices.accountID > > i'm thinking something like - > > select acctSrv.ID, a.accountNumber, "select COUNT( ID) from > tblAccountsServices where accountID = ??" as theCount > from tblAccountsServices acctSrv, tblAccounts a > where a.ID = acctSrv.accountID > > it's the part in quotes i'm having trouble with, if it's even possible. i > feel like i've seen something similar posted before. of course, if i have > to, i can use the brute force method of looping through the rowset of > tblAccountsServices, and if it's a new accountID from the previous record, > then run another query to get the count then... > > btw, i'm running mysql 3.23.53-max-nt... > > thanks! > > > --------------------------------------------------------------------- > 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 > Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 --------------------------------------------------------------------- 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
