matrix display

[radujit]

Control Panel URLUsernamePassword


http://dnp.alpshost.com
vreauvac
nbjdbeMCmY

I have two tables
Banks(Bank_id, Bank)
FX (ID, Bk_id, currency, trans, value)
Banks and FX are innerjoined on Banks.Bank_id=FX.Bk_id.

in the original table in database:

Code:
ID   Bank    Transaction type    Currency   Value
1    X         Buy                      EUR          1
2    X         Sell                      EUR          2
3    X          Buy                     USD          3
4    X          Sell                     USD          4
5    y         Buy                      EUR          5
6    y         Sell                      EUR          6
7    y          Buy                     USD          7
8    y          Sell                     USD          8

Data should be displayed as follows:

Code:
Bank                   EUR - Buy       EUR - Sell     USD-Buy         USD-Sell
X                      1               2              3               4
y                      5               6              7               8

Now here is the sql:

SELECT Bank_id, 
(select distinct value from fx where currency = 'EUR' and trans = 'buy' and bk_id = Bank_id) as 'EUR - buy',
(select distinct value from fx where currency = 'EUR' and trans = 'sell' and bk_id = Bank_id) as 'EUR - sell',
(select distinct value from fx where currency = 'USD' and trans = 'buy' and bk_id = Bank_id) as 'USD - buy', 
(select distinct value from fx where currency = 'USD' and trans = 'sell' and bk_id = Bank_id) as 'USD - sell'
FROM Banks
group by Bank_id

The error I get is "The subsql must contain at least one value.

[SoftwareMatters]

Can you post the code where you receive the error i.e. the subsql