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
