matrix sql display

[radujit]

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.

[pritaeas]

If your table is set to case-sensitive, then you must use 'Buy' and 'Sell'

[radujit]

I tried, still same error.

[pritaeas]

Try the queries separately and see what the result is, there should be at least one record returned for this to work.

[smantscheff]

At my system it works:

drop table if exists banks;
create table Banks(Bank_id integer, Bank text);
drop table if exists FX;
create table FX (ID integer, Bk_id integer, currency text, trans enum('Buy','Sell'), value integer);
insert into Banks values (1,'X'),(2,'Y');
insert into FX values 
(1,1,'EUR','Buy',1),
(2,1,'EUR','Sell',2),
(3,1,'USD','Buy',3),
(4,1,'USD','Sell',4),
(5,2,'EUR','Buy',5),
(6,2,'EUR','Sell',6),
(7,2,'USD','Buy',7),
(8,2,'USD','Sell',8);

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
;

+---------+-----------+------------+-----------+------------+
| Bank_id | EUR - buy | EUR - sell | USD - buy | USD - sell |
+---------+-----------+------------+-----------+------------+
|       1 |         1 |          2 |         3 |          4 |
|       2 |         5 |          6 |         7 |          8 |
+---------+-----------+------------+-----------+------------+

So I assume the problem is in your test data.
Its a problematic query, anyway, because it will give no or false results if there isn't exactly one EUR/USD buy/sell row for each bank.