SQL - Selecting two Columns with two different conditions

user_8684704 0
Viewed 408

I'm trying to select 2 columns with 2 different conditions on the same table with a single query on SQL.

My table structure is like below

| id | from_cur | to_cur | cur_rate |
|----|----------|--------|----------|
| 1  | BTC      | USDT   | 1.2      |
| 3  | BTC      | BUSD   | 1.5      |
| 4  | BTC      | EUR    | 2.4      |
| 4  | BTC      | BNB    | 1.6      |
| 5  | BTC      | BTC    | 1        |
| 6  | BNB      | USDT   | 1.8      |
| 7  | BNB      | BUSD   | 1.02     |
| 8  | BNB      | EUR    | 2.34     |
| 9  | BNB      | BTC    | 1.34     |
| 10 | BNB      | BNB    | 1        |

I need to display the cur_rate for from_cur and to_cur from the user input and also need to display the cur_rate for the default to_cur consider as BTC

This is the result I want if the from_cur is BNB and to_cur is EUR

 | cur_rate | default_rate |
 |----------|--------------| // cur_rate = BNB to EUR
 | 2.34     | 1.34         | // default_rate = BNB to BTC

The following is the query that I have tried

SELECT   
     CASE
            WHEN to_cur = 'EUR'
                THEN cur_rate
            ELSE 0
            END AS cur_rate,
     CASE
            WHEN to_cur = 'BTC'
                THEN cur_rate
            ELSE 0
            END AS default_value
FROM currency_rates
WHERE from_cur = 'BNB'

It is resulting in n rows with 0 like below.

+----------+---------------+
| cur_rate | default_value |
+----------+---------------+
|        0 |          1.34 |
|        0 |             0 |
|     2.34 |             0 |
|      .   |          .    |
|      .   |          .    |
|      0   |          0    |
+----------+---------------+

I don't want entries with 0 to be displayed, so tried to use CASE statement alias in WHERE clause but that doesn't worked since WHERE is computed before SELECT list.

Can anyone please suggest how can I achieve this in SQL with single query?

where-clause mysql sql
3 Answers

you can use following query

SELECT   
     max(CASE WHEN to_cur = 'EUR' THEN cur_rate ELSE 0 END) as cur_rate,
     max(CASE WHEN to_cur = 'BTC' THEN cur_rate ELSE 0 END) as default_value
FROM currency_rates 
WHERE from_cur = 'BNB'
user_150195360 
select c.cur_rate, c2.cur_rate default_value 
from currency_rates c 
    join currency_rates c2 on c.from_cur=c2.from_cur and c2.to_cur='BTC' 
where c.from_cur='BNB' and c.to_cur='EUR'
user_74810190

Could you clarify how this might be used? The below might serve if you are retrieving the output for a single combination of from_cur and to_cur:

select  
     a.cur_rate
    ,b.default_rate
from
(
select 
    cur_rate
from
    table
where
    from_cur = from_cur_inp #'BNB'
and 
    to_cur = to_cur_inp #'EUR'
)a
cross join
(
select
    cur_rate as default_rate
from
    table
where
    from_cur = from_cur_inp #'BNB'
and
    from_cur = default_cur #'BTC'
)b
user_46202510
Related