Search code examples
mysqljoin

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

Solution

  • ====================== SOLUTION IMPROVED ==================

    UPDATE `tradebook` AS `atb`
JOIN (
    SELECT 
        `bv`.`Ticker`,
        MAX(CASE WHEN `bv`.`Vol_ref` = '10m-1h' THEN `bv`.`Volatility` END) AS `vol_10m_1h`,
        MAX(CASE WHEN `bv`.`Vol_ref` = '10m-30m' THEN `bv`.`Volatility` END) AS `vol_10m_30m`,
        MAX(CASE WHEN `bv`.`Vol_ref` = '5m-1h' THEN `bv`.`Volatility` END) AS `vol_5m_1h`,
        MAX(CASE WHEN `bv`.`Vol_ref` = '5m-30m' THEN `bv`.`Volatility` END) AS `vol_5m_30m`
    FROM (
        SELECT 
            `bv1`.`Ticker`,
            `bv1`.`Volatility`,
            `bv1`.`Vol_ref`
        FROM `volatility` AS `bv1`
        WHERE `bv1`.`Database_time_milsec` = (
            SELECT MAX(`bv2`.`Database_time_milsec`)
            FROM `volatility` AS `bv2`
            WHERE `bv2`.`Ticker` = `bv1`.`Ticker` AND `bv2`.`Vol_ref` = `bv1`.`Vol_ref`
        )
    ) AS `bv`
    GROUP BY `bv`.`Ticker`
) AS `bv` ON `atb`.`Ticker` = `bv`.`Ticker`
SET 
    `atb`.`10m_1h_vol` = IFNULL(`atb`.`10m_1h_vol`, `bv`.`vol_10m_1h`),
    `atb`.`10m_30m_vol` = IFNULL(`atb`.`10m_30m_vol`, `bv`.`vol_10m_30m`),
    `atb`.`5m_1h_vol` = IFNULL(`atb`.`5m_1h_vol`, `bv`.`vol_5m_1h`),0
    `atb`.`5m_30m_vol` = IFNULL(`atb`.`5m_30m_vol`, `bv`.`vol_5m_30m`);