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

Solution 1

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 ...

Solution 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'