CODEWITHSUNDEEP
sqlsql-serverleft-joininner-join
Narendra Jangir
Narendra Jangir

Reputation: 241

Want aggregated data from two tables based on specific condition (SQL conditional join)

I have two tables.

T_MarkPrice:

+--------+--------+-------+------------+
| FundID | Symbol | Price | Date       |
+--------+--------+-------+------------+
| 0      | DELL   | 10    | 2014-12-09 |
| 1      | DELL   | 11    | 2014-12-09 |
| 2      | DELL   | 12    | 2014-12-09 |
+--------+--------+-------+------------+

T_Data:

+--------+--------+------------+
| FundID | Symbol | Date       |
+--------+--------+------------+
| 1      | DELL   | 2014-12-09 |
| 2      | DELL   | 2014-12-09 |
| 3      | DELL   | 2014-12-09 |
+--------+--------+------------+

I want aggregated data from both the tables in the following formats

Symbol and date are necessary conditions to match (i.e. Symbol and date of T_Data should match with T_MarkPrice).

FundID match is an optional condition, If fundID of T_Data is available in T_MarkPrice then pick Price from that row otherwise pick price from the row with fundID 0 matching date and symbol.

Here we can see that fundID 3 is not available in T_MarkPrice table, in this case we have to pick markprice with fundID 0.

Output should be following-

+--------+--------+------------+-------+
| FundID | Symbol | Date       | Price |
+--------+--------+------------+-------+
| 1      | DELL   | 2014-12-09 | 11    |
| 2      | DELL   | 2014-12-09 | 12    |
| 3      | DELL   | 2014-12-09 | 10    |
+--------+--------+------------+-------+

Upvotes: 1

Views: 41

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95062

Join both the default price record and the matching record to your data record. Then show the matching price if such price exists, else show the default price.

select 
  d.fundid, 
  d.symbol, 
  d.date,
  coalesce(mp.price, mpdef.price) as price
from t_data d
left join t_markprice mpdef on 
  mpdef.symbol = d.symbol and mpdef.date = d.date and mpdef.fundid = 0
left join t_markprice mp on
  mp.symbol = d.symbol and mp.date = d.date and mp.fundid = d.fundid;

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

You can LEFT JOIN the two tables, and use COALESCE to replace the price for the unmatched row with the value of the price for fundId = 0, something like this:

SELECT 
  d.FundID,
  d.Symbol,
  d.Date,
  COALESCE(p.price, (SELECT Price
                     FROM T_MarkPrice
                     WHERE FundID = 0)) AS Price
FROM T_Data AS d 
LEFT JOIN T_MarkPrice AS p  ON p.FundID = d.FundID 
                            AND p.Symbol = d.Symbol
                            AND p.Date = d.Date;

The LEFT JOIN will make unmatched rows like the row with FundID = 3 be included in the result set, and it will have price = null, then the COALESCE will replace the nullable price value for these unmatched rows with the price value for the fundid = 0 from the t_markprice table.

This will give you:

| FUNDID | SYMBOL |       DATE | PRICE |
|--------|--------|------------|-------|
|      1 |   DELL | 2014-12-09 |    11 |
|      2 |   DELL | 2014-12-09 |    12 |
|      3 |   DELL | 2014-12-09 |    10 |

Upvotes: 0

Related Questions