[Emerald] SQL Help

Will LaSala ( will@greennet.net )
Fri, 5 May 2000 14:37:05 -0400

Hi all,
I'm having a bit of a problem with a query I'm writing and I was wondering
if anyone could help me out.
The query should return a complete list of over usage charges with info
about the customer who incurred them without duplicating themselves
Here's what I have

SELECT Charges.ChargeID, MasterAccounts.PayMethod, Charges.CustomerID,
Charges.Amount, Charges.Description, Charges.LastModifyDate,
SubAccounts.Email, MasterAccounts.FirstName, MasterAccounts.LastName,
MasterAccounts.Company, SubAccounts.SendBill
FROM Charges
INNER JOIN MasterAccounts ON Charges.CustomerID = MasterAccounts.CustomerID
INNER JOIN SubAccounts ON MasterAccounts.CustomerID =
SubAccounts.CustomerID
WHERE Charges.Description Like "%5/1/00 Usage:%"
AND SubAccounts.Email<>""
AND SubAccounts.SendBill=1
ORDER BY Charges.CustomerID, MasterAccounts.PayMethod, Charges.Amount,
Charges.Description, SubAccounts.Email

Now this works well unless....two things...if someone doesn't have one
single sub account checked with sendbill (another words they have multiple
subaccounts and more then one is checked off with send bill, which I only
want one sub account for each charge) and if someone doesn't have sendbill
checked off for any of the subaccounts

Does anyone know of a query that will work around either of these
situations?
I more interested in the 2nd of the 2 (if someone doesn't have send bill
checked off for any of the subaccounts)

Thanks
Will ,,,=^. .^=,,,
Webmaster
GreenNet® ==========================================
"Link Locally ~ Surf Globally©" http://www.greennet.net
Come Play On The Best Quake Server in the Northeast
Serving Eastern Massachusetts and Southern New Hampshire
TEL: 978-363-8898 FAX: 978-363-1225 email:will@greennet.net
===================================================

For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart