I have 2 tables transaction, account Transaction table ------------------------------------------------------------------------------------------------ id, account_no, date, transaction_type, amount, balance ---------------------------------------------------------------------------------------------- 1 0000012 2011-08-02 Withdrawal 2000 40000 2 0000023 2011-08-03 Withdrawal 200 1400 3 0000022 2011-08-04 Deposit 1000 200 4 0000890 2011-08-12 Withdrawal 250 3200 5 0000012 2011-08-23 Withdrawal 1000 3000 6 0000012 2011-08-28 Withdrawal 500 2500 7 0000012 2011-08-29 Withdrawal 1000 1500 Account table ------------------------------------------------------------------------------------------- account_no, account_name, balance, account_type ------------------------------------------------------------------------------------------ 0000012 John Kelvin 1500 Savings 0000890 Terry K 2000 Current 0000023 Peter Godman 2000 Savings 0000022 Godwin Rodman 1300 Savings I want to write an sql to Get all the savings accounts that did not withdrew more than 2 times between the beginning and end of the month. Note: Savings Accounts that did not withdraw at all for that month should also be selected
SELECT Account.account_no FROM Account LEFT JOIN (SELECT Transaction.account_no FROM [Transaction] WHERE (((Transaction.transaction_type)="Withdrawal")) GROUP BY Transaction.account_no HAVING (((Count(Transaction.id))>2))) AS subMultipleWithdrawals ON Account.account_no = subMultipleWithdrawals.account_no WHERE (((Account.account_type)="Savings") AND ((subMultipleWithdrawals.account_no) Is Null)); PHP: