Mysql Question posted in
The official MySQL documentation can be found here.

JOIN in mySQL with a subquery and WHERE clause

My JOIN does not work. The data stays NULL

Hi, this is my first question, so please be nice. Think twice before hitting the "minus" button.

1/ I have a volatility AS bv table with he following fields:

  • bv.Ticker
  • bv.Vol_ref
  • bv.Volatility
  • bv.Database_time_milsec

2/ bv.Vol_ref can be 1 of 4 : ’10m-30m’, ‘5m-30m’, ‘5m-1h’ or ’10m-1h’.

3/ I have a tradebook AS atb table that i want to fill with onfo from bv.

  • atb.10m_1h_vol : the last bv.Volatility where atb.ticker = bv.Ticker and bv.Vol_ref=’10m-1h’
  • atb.10m_30m_vol : the last bv.Volatility where atb.ticker = bv.Ticker and bv.Vol_ref=’10m-30m’
  • atb.5m_1h_vol : the last bv.Volatility where atb.ticker = bv.Ticker and bv.Vol_ref=’5m-1h’
  • atb.5m_30m_vol : the last bv.Volatility where atb.ticker = bv.Ticker and bv.Vol_ref=’5m-30m’

My strategy is :
1/ get the latest bv.Database_time_milsec per bv.Vol_ref and bv.Ticker`

SELECT
        Ticker,
        MAX(CASE WHEN Vol_ref = '10m-30m' THEN `Database_Time_milsec` END) AS `max_time_10m_30m`,
        MAX(CASE WHEN Vol_ref = '5m-30m' THEN `Database_Time_milsec` END) AS `max_time_5m_30m`,
        MAX(CASE WHEN Vol_ref = '5m-1h' THEN `Database_Time_milsec` END) AS `max_time_5m_1h`,
        MAX(CASE WHEN Vol_ref = '10m-1h' THEN `Database_Time_milsec` END) AS `max_time_10m_1h`
    FROM
        volatility
    GROUP BY
        `Ticker`
) AS bv_max_time

2/ use these max time to get the latest vol and insert it:

UPDATE `tradebook` AS atb
JOIN (
    SELECT
        Ticker,
        MAX(CASE WHEN Vol_ref = '10m-30m' THEN `Database_Time_milsec` END) AS `max_time_10m_30m`,
        MAX(CASE WHEN Vol_ref = '5m-30m' THEN `Database_Time_milsec` END) AS `max_time_5m_30m`,
        MAX(CASE WHEN Vol_ref = '5m-1h' THEN `Database_Time_milsec` END) AS `max_time_5m_1h`,
        MAX(CASE WHEN Vol_ref = '10m-1h' THEN `Database_Time_milsec` END) AS `max_time_10m_1h`
    FROM
        volatility
    GROUP BY
        `Ticker`
) AS bv_max_time ON atb.`ticker` = bv_max_time.`Ticker`
JOIN `volatility` AS bv ON atb.`ticker` = bv.`Ticker`
SET 
    atb.`10m_1h_vol` = CASE WHEN bv.`Database_Time_milsec` = bv_max_time.`max_time_10m_1h` THEN bv.`Volatility`  END,
    atb.`10m_30m_vol` = CASE WHEN bv.`Database_Time_milsec` = bv_max_time.`max_time_10m_30m` THEN bv.`Volatility`  END,
    atb.`5m_1h_vol` = CASE WHEN bv.`Database_Time_milsec` = bv_max_time.`max_time_5m_1h` THEN bv.`Volatility` END,
    atb.`5m_30m_vol` = CASE WHEN bv.`Database_Time_milsec` = bv_max_time.`max_time_5m_30m` THEN bv.`Volatility`  END;

but it does not work. The data does not get joined and the atb fields stay NULL

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    ====================== SOLUTION FOUND ==================

    I ditched the JOIN technique, although it is my favourite and went nuclear. It is NOT elegant, but it works:

    UPDATE tradebook AS atb 
SET 
    atb.10m_1h_vol = (
        SELECT bv.Volatility 
        FROM volatility AS bv 
        WHERE bv.Ticker = atb.Ticker 
        AND bv.Vol_ref = '10m-1h' 
        ORDER BY bv.Database_time_milsec DESC 
        LIMIT 1
    ) 
WHERE 
    atb.10m_1h_vol IS NULL;

UPDATE tradebook AS atb 
SET 
    atb.10m_30m_vol = (
        SELECT bv.Volatility 
        FROM volatility AS bv 
        WHERE bv.Ticker = atb.Ticker 
        AND bv.Vol_ref = '10m-30m' 
        ORDER BY bv.Database_time_milsec DESC 
        LIMIT 1
    ) 
WHERE 
    atb.10m_30m_vol IS NULL;

UPDATE tradebook AS atb 
SET 
    atb.5m_1h_vol = (
        SELECT bv.Volatility 
        FROM volatility AS bv 
        WHERE bv.Ticker = atb.Ticker 
        AND bv.Vol_ref = '5m-1h' 
        ORDER BY bv.Database_time_milsec DESC 
        LIMIT 1
    ) 
WHERE 
    atb.5m_1h_vol IS NULL;
    
UPDATE tradebook AS atb 
SET 
    atb.5m_30m_vol = (
        SELECT bv.Volatility 
        FROM volatility AS bv 
        WHERE bv.Ticker = atb.Ticker 
        AND bv.Vol_ref = '5m-30m' 
        ORDER BY bv.Database_time_milsec DESC 
        LIMIT 1
    )     
WHERE 
    atb.5m_30m_vol IS NULL;

    comments and improvement welcome


  2. 0

    You attempt to update all the atb columns for each bv row with the ticker. It looks to me like if the set...case when happens to be true for the last bv row it encounters (where no order is guaranteed) it will set a value, otherwise it will leave it null.

    You would need to get the volatility for the max times in your subquery to do this (and not join bv outside the subquery), either using window functions or using substr(max(case...then concat... as shown in https://stackoverflow.com/a/72568199 (exactly how is dependent on what type Database_time_milsec is).

    Or alternatively, clear all the atb values first, and then have your update set them conditionally, e.g.:

    set atb.`10m_1h_vol`=coalesce(case when...end, atb.`10m_1h_vol`),
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search