SQL: filter on a combination of two column values

Question

I have a table balances with the following columns:

bank | account | date | amount

I also have a table accounts that has bank and account as its composite primary key.

I want to do a query like the following pseudocode:

select date, sum(amount) as amount from balances where 
bank and account in (list of bank and account pairs) group by date

The list of bank-account pairs is supplied by the client. How do I do this? Create a temp table and join on it?

I'm using Sybase

Answer 1

An inner join would enforce the bank+account combination, but you have to be careful about getting more rows back than you expect, especially when doing sums.

Something like the following could work, although it'll depend if it's supported in Sybase

select b.date, sum(b.amount) as amount
from balances as b
where exists (select 1 from backaccounts as ba where ba.bank = b.bank and ba.account = b.account)
group by b.date

Answer 2

It may be helpful to have some more information.

If you have criteria that are driving your particular list of bank and account entities then you should be joining on these tables.

You do have a Bank table and an Account table don't you?

Assuming you have the information in the accounts table that narrow down the specific accounts you want to reference, for example suppose your Accounts table has an IsActive char(1) NOT NULL field and you want the balances for inactive accounts you would write something like this:

SELECT date, sum( amount ) AS amount
FROM Balances b 
     INNER JOIN Accounts a 
     ON b.Bank = a.Bank AND b.Account = a.Account
WHERE a.IsActive = 'N'

From a design perspective your should probably have created an artificial key to remove replication of non-identifying data across tables. This would give you something like this:

CREATE TABLE Accounts ( 
    AccountId int identity(1,1) NOT NULL,
    Bank nvarchar(15) NOT NULL,
    Account nvarchar(15) NOT NULL
)

CREATE TABLE Balances ( 
    AccountId int,
    Date datetime, 
    Amount money
)

This allows errors in the Bank or Account fields to be edited without having to cascade these changes to the Balances table as well as a slightly simpler query.

SELECT date, sum( amount ) AS amount
FROM Balances b 
     INNER JOIN Accounts a 
     ON b.AccountId = a.AccountId
WHERE a.IsActive = 'N'

Answer 3

If bank account pairs is known in you app you just write:

(bank = 1 AND account = 2) OR (bank = 3 AND account = 4) OR ...

If list of bank account pairs is a subquery, then write something like this:

SELECT * FROM balances b LEFT JOIN bank_account_pairs p ON ...
WHERE b.bank = p.bank AND b.account = p.account AND ...

Answer 4

If you didn't want to do a temp table, one solution would be something like this:

select bank, account, sum(amount)
from balances,
(select 'XXX' as bank, 13232 as account
  union all
 select 'YYY' as bank, 138232 as account
  union all
 select 'ZZZ' as bank, 183823 as account) banks
where balances.bank = banks.bank
  and balances.account = banks.account

If your app can handle it, the best option is probably like this:

select bank, account, sum(amount)
from balances
where (bank = 'XXX' and account = 123i832)
    or (bank = 'YYY' and account = 28323)
    or (bank = 'ZZZ' and account = 2839283)