Scott Haneda wrote:
4.0.18-standard-log
I have a very basic one to many relationship, accounts and transactions.
There is only one account per users, but of course, there can be x
transactions. These are once a month charges for billing.
I need to be able to select all accounts where next_charge_date <= NOW()
That's the easy part, I get all the records I want. However, some of those
get charges through one gateway, and some get charged through another.
The transaction table has a field called merchant, lets say it can be bankA
or bankB.
So, I need a list of accounts, where none of its "many" transaction records
has the merchant bankA.
Wouldn't something like this suit your needs?
SELECT a.account_id
FROM accounts a
LEFT JOIN transactions t
ON a.account_id = t.account_id
AND t.next_charge_date <= NOW()
AND t.merchant != 'bankA'
GROUP BY a.account_id;
--
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Roaming North America, based in Columbus, Ohio
email: [EMAIL PROTECTED] mob: +1 614 406 1267
Are You MySQL Certified? http://www.mysql.com/certification
Got Cluster? http://www.mysql.com/cluster
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
