MySQL – Join Two Tables with Mismatched Values

MySQL

I have two tables with >4million records i need to make a select query with where two columns match bring both tables value on this match and then i will insert that into a 3rth table:

This is table A: (bitfinex)

This is table B: (Kraken)

I need to do a SELECT where timestamp and exchange_pair matches, as you can see in this image from table B the same timestamp can have multiple exchange_pair, what i need to do is match those with the other table columns and bring both table content

this was the query i used at once but when analyzing the results i had some mismatch values on the joins ( i did not found the relation, it was not either timestamp or the exchange pair) i wonder if my query syntax is right for the result im looking for.

INSERT IGNORE INTO spreads(timestamp,exchange_pair,platform_a,platform_b,a_bid_price,b_bid_price, high_value,spread_percentage,spread)
SELECT B.timestamp, B.exchange_pair,'bitfinex' as platform_a,'kraken' as platform_b,
 B.bid,
 K.bid_price,
 (SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)),
 (SELECT (((ABS(K.bid_price - B.bid))*100)/(SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)))),
  ABS(K.bid_price - B.bid)  AS spread
FROM bitfinex AS B
JOIN kraken AS K
 ON B.timestamp = K.timestamp
JOIN kraken AS K2
 ON B.exchange_pair = K2.exchange_pair
WHERE K.exchange_pair = B.exchange_pair AND B.timestamp = K.timestamp ;

im still confused if using inner join or just join, also i added the "Where" clause to make sure exchange pair and timestamp matches.

Best Answer

Point 1 : INNER JOIN & JOIN both are same thing.

Point 2 : No need to do an extra join on K2

You can use this :

INSERT IGNORE INTO spreads(timestamp,exchange_pair,platform_a,platform_b,a_bid_price,b_bid_price, high_value,spread_percentage,spread)
SELECT B.timestamp, B.exchange_pair,'bitfinex' as platform_a,'kraken' as platform_b,
 B.bid,
 K.bid_price,
 (SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)),
 (SELECT (((ABS(K.bid_price - B.bid))*100)/(SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)))),
  ABS(K.bid_price - B.bid)  AS spread
FROM bitfinex AS B
JOIN kraken AS K
 ON B.timestamp = K.timestamp
and 
 K.exchange_pair = B.exchange_pair  ;

PROPOSED QUERY

select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
union select t1.id,t2.id,NULL,NULL from t1
    inner join t2 using (id)
union select t1.id,t2.id,t3.id,NULL from t1
    inner join t2 using (id)
    inner join t3 using (id)
union select t1.id,t2.id,t3.id,t4.id from t1
    inner join t2 using (id)
    inner join t3 using (id)
    inner join t4 using (id)
;

SAMPLE DATA

use test
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1
(
    id int not null,
    name varchar(20),
    primary key (id)
); 
insert into t1 values (1,'name1');
create table t2 like t1; insert into t2 values (1,'name2');
create table t3 like t1; insert into t3 values (1,'name3');
create table t4 like t1; insert into t4 values (1,'name4');

PROPOSED QUERY EXECUTED

mysql> select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
    -> union select t1.id,t2.id,NULL,NULL from t1
    ->     inner join t2 using (id)
    -> union select t1.id,t2.id,t3.id,NULL from t1
    ->     inner join t2 using (id)
    ->     inner join t3 using (id)
    -> union select t1.id,t2.id,t3.id,t4.id from t1
    ->     inner join t2 using (id)
    ->     inner join t3 using (id)
    ->     inner join t4 using (id)
    -> ;
+-------+-------+-------+-------+
| t1_id | t2_id | t3_id | t4_id |
+-------+-------+-------+-------+
|     1 |  NULL |  NULL |  NULL |
|     1 |     1 |  NULL |  NULL |
|     1 |     1 |     1 |  NULL |
|     1 |     1 |     1 |     1 |
+-------+-------+-------+-------+
4 rows in set (0.00 sec)

mysql>

All the columns have id as a common name. If you want, I can adjust my answer with the id names

MySQL – Join Two SELECT Queries with Aliases

It would be better to simplify the query without nested levels of subqueries. You already have as simple table (t1) joined to a derived table (t2). Join them further to the other query2 as a derived table (lets call it t3 in lack of a better name):

SELECT 
    t1.*, 

    t2.received_quantity,
    t2.ordered_quantity,
    t2.unit_cost,

    t3.open_quantity
FROM 
        item_master AS t1
    INNER JOIN
        (
        SELECT item_master_id,
           SUM(received_quantity) AS received_quantity,
           SUM(ordered_quantity)  AS ordered_quantity,
           SUM(unit_cost)         AS unit_cost
        FROM material_line_item
        GROUP BY item_master_id
        ) AS t2
    ON t1.id = t2.item_master_id

    INNER JOIN
        (
        SELECT item_master_id,
               SUM(open_quantity) AS open_quantity
        FROM closed_stock
        GROUP BY item_master_id
        ) AS t3
    ON t1.id = t3.item_master_id ;

If there are items in the item_master table that don't appear in one or both the other two tables, you might want to change these INNER joins to LEFT joins.

Best Answer

Related Solutions

MySQL Join – Forcing NULL Rows in Joins

PROPOSED QUERY

SAMPLE DATA

PROPOSED QUERY EXECUTED

MySQL – Join Two SELECT Queries with Aliases

Related Question