A little rusty on subqueries - I have two tables (masteraccount, and
subaccount) I want to pull some information from both tables in one query,
but my subquery is returning more than one value so I error on the query.
When all is said and done I want information from the master account (1
record) and the login name from the subaccount that has the sendbill
flagged, along with the sum of the cost of all sub accounts.
Any ideas? Hope I've made sense.
Jason
SELECT SA.CustomerID,
sum(SA.Cost) as AccountCost,
(SELECT SA.Login
FROM dbo.SubAccounts SA,
dbo.MasterAccounts MA
WHERE SendBill = 1
AND MA.maExpireDate <=
'#DateFormat(Now(), "mm/dd/yyyy")#'
AND MA.CreditCardAutoBill = 1
AND ma.CustomerID = sa.CustomerID
AND ma.customerid = 294
) as Login
FROM dbo.SubAccounts SA,
dbo.MasterAccounts MA
WHERE MA.maExpireDate <= '#DateFormat(Now(), "mm/dd/yyyy")#'
AND MA.CreditCardAutoBill = 1
AND ma.CustomerID = sa.CustomerID
AND ma.customerid = 294
GROUP BY SA.CustomerID
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
